469,924 Members | 1,387 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

Copying and pasting information from excel to access

I have an excel spreadsheet with 500 rows of information. I want to copy and paste that information into an access table that i'm using, column by column.... But access only lets me copy and paste cell by cell from excel to access. Of course i could do that or even use one of my forms i created but that only allows me to import the information record by record, but that would take for ever when i'd like to do the 500 records in a few big scoops (column by colunm).
I've tried using the template of my access table, putting the information into it using excel and then importing the excel speadsheet but it won't "update" the current access table, it simply "overwrites" or "adds" another table...

How can I quickly and easily copy and paste chunks of information from excel to an existing access table ?

Thanks for your time
May 22 '07 #1
3 30490
I have an excel spreadsheet with 500 rows of information. I want to copy and paste that information into an access table that i'm using, column by column.... But access only lets me copy and paste cell by cell from excel to access. Of course i could do that or even use one of my forms i created but that only allows me to import the information record by record, but that would take for ever when i'd like to do the 500 records in a few big scoops (column by colunm).
I've tried using the template of my access table, putting the information into it using excel and then importing the excel speadsheet but it won't "update" the current access table, it simply "overwrites" or "adds" another table...

How can I quickly and easily copy and paste chunks of information from excel to an existing access table ?

Thanks for your time

i normally link the database to the spreadsheet using the tables link wiz then once done use append to transfer the data over

G
May 22 '07 #2
jamjar
50
i normally link the database to the spreadsheet using the tables link wiz then once done use append to transfer the data over

G
You can't copy and paste (as you've found out).
You have to import or link the data, then as G suggests, use an append query to add it to your table.
It sounds like you've already figured out how to import the data. And I assume you already have a table set up to which you'd like to add the new data.
  1. Use a query wizard to create a query based on the new table, choosing all the fields you want. (If you want all the fields, just hit >>).
  2. Finish the wizard and see if you're happy with what its showing (should be good at this point).
  3. Click the Design button (top left toolbar, looks like a blue triangle with a pencil above it).
  4. Click the Query Type button (middle of toolbar, looks like two spreadsheets).
  5. Select Append Query
  6. Select the table you want to append to.
  7. For each field, select the appropriate field to Append to (4th row down in the lower section of the form).
  8. Click the Run button (middle of toolbar, looks like a red !)
  9. You should receive a prompt asking you to confirm you wish to append x number of rows ....
Step 7 will be easy if you used the same Field Names (column headings) in your tables.
If it all works out OK, you can save this query so you don't have to create it again. Just import the new data to the same new data table each time, overwriting the old one, and run the query. Just be careful not to run it twice, or you'll duplicate your data (unless you have a primary key to prevent that .... !)

James
May 23 '07 #3
You can't copy and paste (as you've found out).
You have to import or link the data, then as G suggests, use an append query to add it to your table.
It sounds like you've already figured out how to import the data. And I assume you already have a table set up to which you'd like to add the new data.
  1. Use a query wizard to create a query based on the new table, choosing all the fields you want. (If you want all the fields, just hit >>).
  2. Finish the wizard and see if you're happy with what its showing (should be good at this point).
  3. Click the Design button (top left toolbar, looks like a blue triangle with a pencil above it).
  4. Click the Query Type button (middle of toolbar, looks like two spreadsheets).
  5. Select Append Query
  6. Select the table you want to append to.
  7. For each field, select the appropriate field to Append to (4th row down in the lower section of the form).
  8. Click the Run button (middle of toolbar, looks like a red !)
  9. You should receive a prompt asking you to confirm you wish to append x number of rows ....
Step 7 will be easy if you used the same Field Names (column headings) in your tables.
If it all works out OK, you can save this query so you don't have to create it again. Just import the new data to the same new data table each time, overwriting the old one, and run the query. Just be careful not to run it twice, or you'll duplicate your data (unless you have a primary key to prevent that .... !)

James
Nice one J, That really helped, cheers ! : )
May 23 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by postings | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.