Copying from Excel into Access

caferacer

Registered User.
Local time
Today, 19:03
Joined
Oct 11, 2012
Messages
96
Hi,

I am trying to create some additional records in an existing Access table by copying (Ctrl C) the data from the excel rows and pasting into the table using paste append.

All that is happening is Access is creating the additional auto number rows, but the data I want copied over does not appear? The data to be copied is just regulars numbers, no weird formatting etc….

I have also created another table and added the data I want copied across and tried to use an append query, but I am getting some strange results. I am sure it’s something basic I am doing wrong with the query, but It all seems quite complicated for such a simple task, am I missing something?

Thanks
 
You can also use the TransferSpreadsheet action (http://msdn.microsoft.com/en-us/library/aa164087(v=office.10).aspx). If the destination table matches the spreadsheet exactly, you can append the records directly. If not, you can go the much safer route of either importing as a new table, or importing to an existing 'raw data' table. Either way, you then use THAT to update the actual table with an append query.
 
Thanks, got the second method to work oaky.

Seems a lot of bother through for just adding a few records.

Regards
 
You have to keep in mind that while a table's datasheet view can LOOK like a spreadsheet, it really is not, nor does it work like one. Each field is a discrete object, and each record exists independently of the others. On top of that, you can technically only add one record at a time. That's why you can't just copy and paste from Excel into Access.

What TransferSpreadsheet does is copy each cell individually into the matching field in the destination table, one at a time, with a new record created for each row. It just does it really, really fast.
 

Users who are viewing this thread

Back
Top Bottom