Importing data

[rocklee]

Hi,

I'm trying to import some data saved in CSV format delimited by comma.

In fact it is the same file exported with the same database.

This is what I use to import data to my Student table :

DoCmd.TransferText acImportDelim, , "Student", strFullPath, True

This will add new records that doesn't exist in the current student table but will not update the existing records.

For example, if student "AAA, AAA" marries "BBB BBB" her new name is "AAA, BBB" on my external CSV file, on the database it is still "AAA, AAA". They both have the same ID key. When I import it into my database however nothing changes.

I have another option that will delete the existing table and replace it with the imported one, but that isn't the same because there may be existing records in my database that I want to keep.

My query is, how do I import a student table that will also update existing records?

 

[Galaxiom]

Import the new records first then run an update query on the Student table from the csv file.

 

[rocklee]

Thanks mate, what does an update query look like in Access basic?

 

[Galaxiom]

Assuming you can build an update query in the query designer, the easiest way forward is to put this sql into the RunSql method.

DoCmd.RunSQL "UPDATE tablename SET etc"

The Execute method is more sophisticated but the I recommend starting with RunSQL because the code can be taken straight out of the query builder without having to understand anything new.
Learn the Execute technique later.

 

[rocklee]

Don't worry I got the answer :

DoCmd.RunSQL ("UPDATE Student INNER JOIN Student_temp ON Student.UPN = Student_temp.UPN SET Student.forename = [Student_temp].[forename];")

Except I probably need to set all the fields in each table right?

 

[rocklee]

Thanks again! I was not sure on the formatting on creating an update query in Access basic but seem to get it now.

The only problem I have now is with CSV which I'll open another query