Updating a table with data from txt file

[Robin Hood]

Can anyone tell me how I can update or append the data in a table with data in the form of a txt file? The data in the text file is not new data to go into the table. Its updating existing data in the table, by entering information such as: Payment, type of payment, amount and date of payment.

At the moment I am going in manually and typing in the data which is taking hours.

I am sure it is possible but just don't have the know how

cheers

Robin

 

[Dennisk]

If this is to be performed regularly then

Initially Create an import specification fo the text file, and an import file

the general outline is as follows

Delete contents of import file

Run import in code using the import specification (if the filename changes then you will need a file browser box to pick up the current name of the text file.

Use an update query , matching the records in the import file (Primary Key) to the main file Primay Key.

 

[Robin Hood]

Hello Dennisk,

Thanks for your reply. The data in the table does not use primary key as they have unique appeal and reference numbers ie; 1202/0123 is that going to make a difference?

cheers

Robin

 

[DCrake]

The data in the table does not use primary key as they have unique appeal and reference numbers ie; 1202/0123

Is this not then a primary key? Is this the key you use to find the record in the table to update with the contents of the text file? If not how are you matching the old with the new?

Also you need to consider new records in the text file that will need to be populated/appended to the access table.

David

 

[Robin Hood]

Hello DCrake,

Thanks for your reply. The text file is not adding any new appeal/reference numbers. What it has is the original appeal/reference numbers ,names, addresses ect. But what needs to be updated in the database is the type of payment, amount and date.
At this moment in time in the database these fields are blank until a payment has been made.

cheers

Robin

 

[DCrake]

So do you have a seperate payments table that has the named fields in it with a PK and FK?

If so then you need to do an append to the payments table from the text file. Use the TransferText command in Macros to test it