Update the table

[Erictsang]

Dear all

I have difficulty to update a table.

Firstly, i imported excel file to Access,named Registration.
After working for a while on around few days, on 1,300 record.

There are update on the original excel file and i need to import again to the Registration.

First i try to update those record that changed but i do not know how to sort out which is updated

Then, i deleted all data and imported again, i thought i can use the new data with the same field names, the existing links are not affected

however, it prompts, can not overwrite it

May i know what went wrong ?!

 

[Minty]

In order to be able to update existing and only import new records, each record in the spreadsheet must have a unique identifier that you also have in the table you created. This is the easiest way.

If not a date or datetime stamp in the original records can be used if you aren't going to do the update more than once a day (for a date stamp), but is more complicated.

 

[arnelgp]

possible solution is to have a continuous Line numbering at column A on your worksheet so that it uniquely identifies each row.

then import the updated worksheet to New table (not registration table).

you can know which one got updated by concatenating each fields in your table, and concatenating each columns in the worksheet then comparing them. use Query to compare and update

Update registration As T1, TempTable As T2 Set T1.Field1=T2.Field1, ... Where (T1.Field1 & T1.Field2 <> T2.Field1 & T2.Field1) And T1.ID = T2.ID;

 

[Erictsang]

Minty

I try to do change the ID in number rather than autonumber and use Append.
But it prompts me with another error.

Frustrated

Eric

 

[Erictsang]

possible solution is to have a continuous Line numbering at column A on your worksheet so that it uniquely identifies each row.

then import the updated worksheet to New table (not registration table).

you can know which one got updated by concatenating each fields in your table, and concatenating each columns in the worksheet then comparing them. use Query to compare and update

Update registration As T1, TempTable As T2 Set T1.Field1=T2.Field1, ... Where (T1.Field1 & T1.Field2 <> T2.Field1 & T2.Field1) And T1.ID = T2.ID;

i think that maybe complicated for me...
I do not know coding, and i have set up all relationship, links, it bother me if the name of databased/table changed

All went wrong, as have lot of queries and forms linked

Eric

 

[arnelgp]

Restore the auto number and create new field that will hold the row number in Ur worksheet. Rename the numbering other than I'd both in table and sheet.

 

[arnelgp]

It's not coding it's update query.

 

[Erictsang]

Still have no idea how?

 

[Erictsang]

I try to do this but fail

Select the excel file, append , but fail