Data Import Problem

[jesusoneez]

I have a reasonably complex database but the problem is with a simple part of it so I'm hoping there's a simple answer. I have a comma delimeted text file created by our mainframe every day that holds our customer account number (primary key) and customer name. This gets imported every day. this table has a one to many link with an Orders table (the many being that an account number can have many orders attached to it). The customer file is generated every day and imported everyday in case new customers have been added to the mainframe system (which isn't a rare occurence). I have to import the customers before the orders due to the relationship.

Problem is, I want the import to run automatically, which is not a problem for my knowledge in itself, but, when importing the data (via a TransferText macro), Access prompts me to either cancel the import or carry on due to duplicate records. How do I get it to append only the new data (if any) to the end of the Customers table without this message popping up. Obviously, I tell access to carry on with the import as this gives me the result I want (new customers in the customers table), but I'd rather this pop up not appear!

Any help much appreciated.

Regards,

Steve

 

[jbg]

convert your macro to VBA and then add the following before the code that runs the append:

DoCmd.SetWarnings = False

Don't forget to set it back to true after the procedure ends

 

[Pat Hartman]

An alternative is to link to the text file and then run an update/append query. See the following for details:

Append and Update in single query

 

[jesusoneez]

Thanks guys. Much appreciated!