Updating part of a 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....

 

[huzza]

Hi Darrell,
what you could do is put a check in your code to see it the key value already exists i.e. check to is if the field causes the key violation already contains the value to be imported. If it does then you want to run an update query instead of an append.
Richard

 

[MoGryph]

Something that might help you out alot, is Replication.

Replication requires some modifications to the database, and I'd definately test it out before you implement it, but- it's a really great feature, and can save from having to write alot of code to take care of merging databases. (It will Merge databases for you).

There are multiple ways you can replicate your database, as well as what we call "Partial Replication".

One of the ways (easiest, but least powerful, and lacking the ability to only replicate certain tables) is the Briefcase. - There's an option during the Install of Access that installs a special feature of the Briefcase that allows you to Merge two databases.

There are other ways that might be more difficult to setup, but which would be better in the long haul. To find out a way that's best for you, goto Help for Microsoft Access, and read through the topic listed under Contents called "Replication".