Append Query duplicate primary key

Mase5701

New Member
Joined
Dec 19, 2018
Messages
6
If I run an append query will it give me an error message if duplicates primary key field?

I have an append query in my database and when I run nothing happens and it doesn't append but it doesn't give me a warning. I gotta figure this out before I start embedding it into macros because if it fails without warning how am I supposed to know?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,434
Office Version
365
Platform
Windows
If I run an append query will it give me an error message if duplicates primary key field?
If you want to see exactly what happens, the best thing to do is create a test situation like that, and see what happens.

Another thing you can do is to create an exact replica of your Append Query, but then change it to a Select query.
Get the count of your final destination table (can use an Aggregate Query or the DCOUNT function in VBA).
Then, get the count of your Select query.
Then, run your Append query.
And get another count of your final destination table.
Compare your counts. If the final number of records minus the count from your Select query does not equal your original count, then some Append records failed.

Of course, you could also do a Select query on your same logic as your Append query, and see if any of those records have the same ID as the records in your destination table.
Get the count of that. And then you could add that check to VBA and return a message, so you can alert the user before running the Append query.

So, there are multiple ways of approaching this (I am sure that there are probably other approaches too).
 
Last edited:

Forum statistics

Threads
1,081,703
Messages
5,360,747
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top