Results 1 to 9 of 9
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    Appending Without Duplicating Records Getting Error

    I am trying to append my table, but without inserting any duplicate records. I have come across a few articles giving some good sql examples, but I've come into a couple of issues.

    1. I can only run the append query once from an sql statement.

    2. I get an error after running it "An action query cannot be used as a row source".



    Here is my sql I am working with:
    Code:
    INSERT INTO QBAppendSELECT * FROM QBAppend_Q
    WHERE (LOGDATE = 03/12/2018)
    AND NOT EXISTS
    ( 
    SELECT * FROM QBAppend_Q
    WHERE QBAppend_Q.InvoiceLineDesc = QBAppend.InvoiceLineDesc
    );
    Now I can run the append query using vba just fine without checking for duplicates, but I need to check for duplicates for when I begin to automate this. Thanks in advance for any help!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Hmmm?
    What is the requirement in plain English?

    Why does Access think this is a rowsource? Where are you using this?
    Does your table have a Primary Key?

    Need more info to give this post some context.

  3. #3
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    QBAppend is a Table with a Primary Key. QBAppend_Q is a Query which is queried from a pass-through query. I am appending QBAppend_Q to QBAppend. I get no errors if I just run the append, but I will if I run the sql statement. If somehow QBAppend gets appended twice, I don't want it appending duplicate records.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    QBAppend is a table in your linked database that has a PK?

    QBAppend_Q is a passthrough query?

    I am appending QBAppend_Q to QBAppend????

    It sounds like you placing a second copy of QBAppend into QBAppend--which is what your duplication seems to indicate also.

    What is the name of the table in your local Access database where (if I understand what you're trying to do)
    you would like to store a copy of the QBAppend data?

    The query actually executes SQL. So I'm a little confused on
    I get no errors if I just run the append, but I will if I run the sql statement.

  5. #5
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    I have a 3 total tables/queries I am using. QBAppend_PT is my passthrough query which is then loaded into the query named QBAppend_Q to add date filters. After QBAppend_Q is generated it is then appended (or should be appended) to QBAppend. To better correct myself in regards to running just the query; If i remove "AND NOT EXISTS" and everything after, then the append will run just fine. As I was waiting for a response, my next guess was that I was trying to append a passthrough query, but after some research, it wasn't entirely the case, but I can see something there has to be causing the error.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If your table has a PK field set to not allow dupes, then what about suppressing the error message you'd get when you try to append duplicates in the field? I don't usually advocate turning off warnings, but have done so for cases like this. The allowable data was always appended and the duplicates were not. Just don't try to use an append sql as a row source and make sure your code turns the warnings back on in the same procedure. An error handler is advised to facilitate this should an unexpected error arise. I can't recall if I ever tried this with the Execute method, but I think not. IIRC, that method stops if an unhandled error is raised, so only part of the data would get processed.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Here is a little of the backstory to explain why I am doing this.
    1. I have QODBC to pull invoices from QuickBooks using ODBC.
    2. I am then going to cross reference those invoices to sales.
    3. The problem is that when I import QuickBooks invoices, the field "RefNumber" (Invoice Number) is pulled as a short-text data type.
    4. I am needing to change the imported "RefNumber" fields' data type to a Number or Double to match the data type of the original sales so I can compare them.

    The only way so far around this (as easy as possible to me) is to create a passthrough query that is appended so I can change the data type in the appended table. The other workaround that I have thought of (as much as I may not want to do it) is to export the passthrough query and re-import using vba and macros.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I am attaching a png of what I think your set up is.
    If you can't change the datatype of RefNumber, then set up another table with the datatype you need and copy the downloaded table to this new table.
    If I have misunderstood your set up then please clarify which tables are where.
    We do not have the intimate details like you do.

    Quickbooks Environment

    Click image for larger version. 

Name:	QBAndAccessSetUp.png 
Views:	14 
Size:	17.8 KB 
ID:	33143
    Access Environment

  9. #9
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Quote Originally Posted by orange View Post
    I am attaching a png of what I think your set up is.
    If you can't change the datatype of RefNumber, then set up another table with the datatype you need and copy the downloaded table to this new table.
    If I have misunderstood your set up then please clarify which tables are where.
    We do not have the intimate details like you do.

    Quickbooks Environment

    Click image for larger version. 

Name:	QBAndAccessSetUp.png 
Views:	14 
Size:	17.8 KB 
ID:	33143
    Access Environment
    You got it correct. I can copy the QB data to a new table, but I need to make sure that either:

    1. All data is re-written (So I will never have duplicates) or
    2. Newest data is appended without duplicating any records.

    I am ultimately needing, in the end, for this to be automated.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append query duplicating records
    By thart21 in forum SharePoint
    Replies: 2
    Last Post: 11-08-2017, 12:16 PM
  2. Replies: 4
    Last Post: 07-24-2015, 07:03 AM
  3. Appending inventory records with current price records
    By sberti in forum Database Design
    Replies: 8
    Last Post: 11-29-2012, 10:24 PM
  4. Replies: 2
    Last Post: 11-19-2012, 01:00 PM
  5. duplicating records
    By kstyles in forum Queries
    Replies: 7
    Last Post: 12-31-2010, 02:31 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums