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

[CJ_London]

easiest way is to create a linked table to the spreadsheet then use an update query where you can append field 'ABC' in your spreadsheet to your field 'XYZ' in your table.

Once the linked table is created, you do not need to delete and recreate next time. Just make sure the spreadsheet has the same name. I tend to use a routine along the lines of

copy spreadsheet to an 'import' folder changing the name to a standard name which your linked table is linked to. e.g. spreadsheets are called 'weekly output for week X' , remove the 'X'
run your import routine
move spreadsheet to an 'imported' folder

 

[kevlray]

I am not sure I can get that to work. The import process will be done by non-technical staff in another office. I will to have work out the code (they just want to push a button and have the magic happen).

 

[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

 

[Pat Hartman]

Yes. The fist solution offered requires no additional code. The poster said "import" but importing isn't necessary. Link to the spreadsheet and create an append query to append the data from the spreadsheet. Start with a select query and select all the columns from the spreadsheet. Change the query type to append. You'll be prompted for the "to" database. Access will then populate automatically all the append to cells where the two tables have matching column names. For the unmatched columns, you have to manually choose the target field. So immediately after the TransferSpreadsheet. line of code, you run your append query. It is always better to do your append this way anyway since the query gives you the ability to do some validation and data clean up on the way in.

I agree with Doc. On THURSDAY, you were given the appropriate solution that didn't require anything fancy. It is now MONDAY and this is the 30th post on the topic. Please try the suggestion to link (more efficient than append) and run an append query.