Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    47

    Unanswered: Key Violation When Running Macro

    Hi,

    I have a bunch of append queries that I got tired of running by themselves one-by-one so I put them into a macro and it worked great. I did some table restructuring and fixed the queries to append where they should. Now, when I went to use the macro I am now getting key violations in tables randomly even though there is not a violation. It isn't always the same table either. The strangest part is if I run it outside of the macro IE run the query by itself it will append just fine. Other times the macro will run great and append properly.

    The exact error is:

    Code:
    Microsoft Office Access can't append all the records in the append query.
    
    Microsoft Office Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 1 record(s) to the table due to key violations, 0 record(s) due to lock 
    violations, and 0 record(s) due to validation rule violations.
    Do you want to run the action query anyway?
    To ignore the error(s) and run the query, click Yes.
    For an explanation of the causes of the violations, click Help.
    Clicking Yes (or No) will continue the macro but won't append anything and clicking Help brings up the generic help for Access.

    I have tried relinking the tables (because they are held in a SQL backend), rewriting the query, remaking the macro, and compacting and repairing the database.

    Any ideas that I may have missed would be helpful.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    My guess would be that some of these queries depend on queries that have previously been run. Access is asynchronous, which is to say, if given a series of commands, it starts to execute one, moves on to the next one and starts executing it, and so forth. It doesn't wait for the first command to be completed before starting the second one, and this can cause timing problems. An example would be a button that runs a series of monthly reports.

    Code:
    DoCmd.OpenReport "ReportA"
    DoCmd.OpenReport "ReportB"
    DoCmd.OpenReport "ReportC"
    Access will attempt to immediately send all three to the printer, and this may possible eat up all the memory assigned to your printing queue, causing an error to occur. The answer is to use DoEvents.

    Code:
    DoCmd.OpenReport "ReportA"
    DoEvents
    DoCmd.OpenReport "ReportB"
    DoEvents
    DoCmd.OpenReport "ReportC"
    DoEvents returns control to Windows, allowing ReportA to complete printing before starting to print ReportB. It then allows ReportB to finish printing before starting ReportC.

    You may sometimes be running into this kind of problem if some of your queries are based on previously run queries, as I said before, and the previous queries haven't finished running before Access starts the next one.

    To be honest, I'm not sure whether or not you can use DoEvents in a Macro; like most Access developers, I very seldom use them.

    Linq ;0)>
    Last edited by Missinglinq; 08-03-10 at 15:29.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2009
    Posts
    47
    Thanks for the idea. My supervisor wants all the append query boxes to have to click yes instead of setting the warnings off. I will try to click them slower first.

    As far as I remember the queries shouldn't depend on the one before them because they pretty much just append to their respective tables. If that doesn't work though I think I am just going to try to put the queries that have the most trouble first and see if that fixes it.

  4. #4
    Join Date
    Jul 2009
    Posts
    47
    Ok well I tried clicking slower which didn't seem to help. Moving the query in the macro didn't work either.

    I then made VBA using the DoEvents command but the same thing still happens.

    Any other ideas?

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Very simply, don't run them in a macro. Instead, run them in VBA using the construct DoCmd.OpenQuery "QueryNameHereInQuotes".

    When using this construct consecutively in VBA, I never have a problem with asynchronosity, even where the queries affect the same objects.

    Good luck,

    SL

  6. #6
    Join Date
    Jul 2009
    Posts
    47
    Thanks for the reply but I've already tried that. I even went as far as using the openquery by itself from a button on the switchboard and the same thing happens. I'm going to try rebuilding the queries next.

    If you have any other ideas I'll try them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •