import from Excel and update and append to many records

[batwings]

Hi there


I have a spreadsheet that I have successfuly imported into Access but now I would like to maintain it, update/append/delete records etc.

The spreadsheet is produced weekly and Intend to import it into my Access database on a weekly basis there are around 20,500 records and 15 Fields. I have kept the Field names in Access the same as the spreadsheet, except that the Access table has an ID field with PK and autonum. There are no other tables involved, it should just be a straight import update append ...but how?

I am looking for the best way to approach carryingout a regular update, is it best to bring the new import into a Temp table? and then carry out the analysis of what has been changed, deleted or added?

Also the queries to do this, how exactly do you get a query to scan through all of these rows and columns.

one last thing is it possible to create a table during the update/append process that will log all of the changes or flag the records using A=Append, D=Deleted U=Updated...

I've tried looking at various forums but mostly all I find is people with similar problems and no definitinve answer.

thanks

Batwings

 

[Guus2005]

Most of the times it is better to use a Temp table. But if the name and location of the spreadsheet doesn't change, and you are using Acc2003 or Acc2007, you can link the spreadsheet and simply refresh the link each time you start the database. In both cases you have a table with the Excel data. From here you can distribute the fields through your database. Note that the imported data in a temp table can be altered and the data linked from an Excel Spreadsheet is Read-Only.

Enjoy!

 

[batwings]

Guus2005

Thanks for the info, I think the Temptable even if it is linked might suit as I have added a couple of columns to the end of my maintable now since the first import.

I really only need to update one field that changes, and of course add new or delete old records.

Batwings