Updating part of record using an imported txt file

[Daz]

Hi,

Does anyone know of an easy way to update part of a record by importing data.

I have managed to create an import\export routine between an outlet based system and an head office based sytem.

Example.

An outlet sells a product and it sends the products data, price etc to head office. Head office read the data into their dbase. This part works fine. This i have done by using a .txt delimited file.

Two days later the outlet realise they have put a wrong figure into their dbase and need to amend their machine and resend the new figures to head office dbase.

This is where the problem appears, when the customers record is already on the head office system,

Every time i have tried import the new data into the head office machine i get the following message.

"MS Access unable to append all the data to the table","1 record was lost due to key violations"

Then a yes\no button appears asking if i want to continue. I press yes the message dissapears, when i look at the record the new data has not been updated, and there is no import error log in the tables window, and the customers record is exactly as originally imported.

The updated .txt file, is all the fields that originally where sent to the head office to create the record, but now one of the field has changed.

Is there a easy way of updating part of a customers record successfully?

Many Thanks

Darrell....

 

[DougP]

First you have to find the culprit record.
If the error say's:
"1 record was lost due to key violations"
then you have a another record somewhare that has either a blank in the key field or is in fact a duplicate.

Do this, Import or link the table in question then create a "find duplicates query" between the 2 tables. That should find the problem record.
you have to use the key field to look at for dups.

DougP, MCP
dposton@universal1.com

Ask me how Bar-codes can help you be more productive.

 

[JerryDennison]

Sounds like you have your tables setup well. The append isn't allowing duplicate records, this is a good thing. Change the append query to an update query and re-run it. To automate the process you may want to have both kinds of queries available and let the user decide if they need to run the update or append version.

Are both db's written in Access? If so, are they identical with the exception of their locations? If so, you may want to consider replicating them. Then any changes made on either end will update between them automatically whenever they are synchronized.