Importing txt file that will update existing records

[NiallCon]

Hi,
I was wondering if anyone could help me plz. I have a very basic access db that contains one table, sample below:-

USN (Primary Key)
MO Code
Received
Captured

The problem I have is the following, each day I have to import a text file that includes a list of USNs with their corresponding MO Codes field either having an 'ok' value or no value. If the MO Code has an 'ok' value everything is fine, however the following I will be importing a new csv file. This new csv file will contain a list of USNs and it is likely that the USN from the previously day will now have an MO Code value of 'ok'. When I try to import the csv file it won't update/overwrite the existing USN because it already exists. I suppose what I need is a way to check the csv file before importing and if the USN exists in the csv file with a value of 'ok' then it should overwrite the null value in the MO Code field in the database.

I don't know if what i said is clear as it's a little bit difficult to explain. I would be grateful for any advice, even if you can point me in the what direction to start in as I really don't know where to start?. I have a little bit of experience of Access from my college days, can access perform this operation or do I have to learn some VB?.

 

[JoanneM]

To keep this simple and use macros you will have to break the problem down

so first you have to import your first .txt file into a TempTable

then create a query linking the USN in the Temp table to your original table

you bring the MO field from the temp table into the query grid and in the criteria type "OK"

you bring the MO field from the original into the query grid and in the criteria type is null

You can then make this an update query

In the MO field of your original table wher it says update
you can either put the reference to the temptable field or just type "OK" and this should do the trick.

to Automate this you can use the macro transfertext and complete the details of the file name and the table name you are going to use.

and then
Setwarnings no
openquery query name
setwarnings yes

save your macro and run PRN

hope this helps

regards

jo



you now have a query that Identifies only records that have null values in the original table and OK values in the temp table

 

[jiqjaq]

When you import data, you are telling Access to create new records. Your problem is that a field defined as the primary can't have duplicates on that field.

What you need to do is set up a separate table to be used just for the imports. Delete any data in the import table before you do the import, so that the records in this table will only have records from the latest import.

Create a query to check for matching USNs between the main table and the import table. Once you're sure that this query works, create a 2nd (delete) query to delete the USN's which match from the 1st query and the main table. Create a 3rd append query to add all the records from the import table to the main table.

There are ways to do this with only 1 query or using VBA code, but these are the basic steps you need to get the job done.