TransferSpreadsheet macro, to Overwrite data not Append To Bottom

[ryguy7272]

I tried to use import an Excel spreadsheet into an existing table, using a
TransferSpreadsheet macro, and it seems to append the data on to the bottom
of the existing table, rather than overwrite the data in the table. I tried
to delete the table, using a DeleteObject macro, and I got a message saying
that the table can’t be deleted because it is ‘participating in one or more
relationships’. Help! How can I refresh the data in an existing table, or
somehow overwrite the data, without adding the data to the bottom of the
table and without deleting the table?


Regards,
Ryan---

 

[Pete D.]

Bring the spreadsheet into another temporary table and use an update query
to update it. You will need to relate the two tables with something that
identifies the unique matching record in each table.

 

[ryguy7272]

Thanks for the tip Pete. I’m not sure how to do that though. Could you list
the steps, or refer me to a web site that explains how to do that? Is there
a way to automate the process?

Regards,
Ryan---

 

[Pete D.]

Since you say you want to update records I assume that the records in access
somehow match the records in the excel sheet. Need to know which column in
the spreadsheet matches which field in the access table. Now as you are
already importing the spreadsheet just do it again but put it in a new table
instead. Next open the relationships window in Access and drag the field
that matches to the other matching field and a line will be drawn between
them relating them together. Next you create a update query using the table
you want to update as the primary table and the table to update from as the
update to table.

Got ya issues
Make sure the two fields you relate together are of the same type (number,
text...)
Make sure you use a backup practice file until you get it right.
More instructions below.
http://www.databasedev.co.uk/update_query.html