Export data to Excel, edit, then re-import to Access? 2

[VBAjedi]

I have a simple table with an auto-numbered key:

Key FName LName

What I'd like to do is export all the records in the table to Excel (accomplished easily by selecting the records and copy/pasting them to Excel as text). Another person will then edit the records in Excel, making changes to some names and adding some records (leaving the Key field blank). I can do all of that.

However, once I'm finished making my changes/additions in Excel, I want to re-import the data to Access. Spreadsheet rows where the Key is listed and exists in the table should update that table row. Spreadsheet rows where the Key field is empty (or there is no matching table Key) should be added (allowing Access to Autonumber those new rows in the Key field).

Using "File>Get External Data>Import. . ." adds new rows easily enough, but throws an error for every row where the Key already exists (which will be most of the time since I will be doing more edits than additions).

How do I set up this import? Will it require VBA code, or am I just using the Import tool incorrectly?

VBAjedi

 

[AlanKW]

Try impoting the spreadsheet into a temporary table then run an append query for the records that have no key field listed and an an update query to update the ones that have a key field.

VBA is optional to automate the process and its simple docmd.openquery "qry_blahblahblah

 

[tledwards]

You need to export the key field so Access knows which field in the spreadsheet record links to the database record so it can update the old record. But the Import will still not update records.

Suggest you use the File> Get External Data> Link to link the spreadsheeet to your database or Import it to a different table. Then use an Append query to add new records and an Update query to make changes to old records.

 

[randy700]

Why are you putting the data in excel for updating. Create a form that displays the records in a datasheet or continuous forms view. This will look to the users just like excel and you don't have to do any exporting/importing.


Randy

 

[VBAjedi]

AlanKW & tledwards,

Sounds like importing to a temporary table, then running two queries is the way to go. Have a star. Will post back if I have any trouble with that approach.

randy700,
The reason I'm exporting to Excel is that there's about 10,000 records, and I want the full power of Excel available for the data editing (Filters, AutoFill, Text To Columns, etc). Most of it could be done with queries, but my users aren't comfortable with that approach. So Excel it is!

VBAjedi