Importing Excel into Access (you think it would be easy)

[CJ_London]

all the code can be behind the button - might just need to provide code for user to select a file

 

[kevlray]

I have the code to select the file. But I will have to do the work to make it a linked table and go from there. I was thinking of another route (almost the same idea). Import the Excel file as is into a 'temporary' table. Then do an append query.

 

[CJ_London]

linking is easy - use transferspreadsheet. Your method will lead to bloat over time

 

[Bullschmidt]

Seems like a lot of good responses above. And now you wrote:

Okay, so back to the subject at hand. Any ideas on how to append the data to the table in question using the spreadsheet provided (with the notes I provide)?

And you originally wrote in part:

I had been using DoCmd.TransferSpreadsheet to import data into a table. But now the user wants to change one of the column headings in Excel to a name that does not match the field name of table.

Well I guess you could still use your DoCmd.TransferSpreadsheet to import data into a table but just make it a new table with all the correct field names. Then create an append query to take what is in the new table and put a copy of those records into your desired table...

 

[Bullschmidt]

And with the Append query the field names don't need to be the same in both tables as they can be individually specified...

 

[kevlray]

Why would my method bloat over time? I import a small amount of data to a temporary table (that I clear out before using). Then use a append query to update the main table (which I have working now).

 

[CJ_London]

Why would my method bloat over time?

because you are creating and deleting objects, the space is not recovered until you do a compact and repair. If you don't believe me, try it. note the sizer of your db, create and populate a table, note the size again, Now delete the table, repeat 2 or 3 times. Has the db shrunk?

You have been given consistent advice - 3 different ways of looking at the excel data (import, link or dao query) then use an append query. We are now on post #29 (after a bit of a diversion), suggest you try it