pasting data from Excel to Access

[SharonMee]

Hello all:

I have a data in excel, I have tried importing it into my Access table but I keep on getting this error and I don't know why. I # of fields are the same, I just can't figure out the gap.

Anyway, I am thinking of copying the data in excel and copying it into my access table in datasheet view, how do I do that, because it won't let me paste all the rows.

Thanks for helping.

SharonMee

 

[dhookom]

Are you "paste appending"? Do you realize you can't "paste update" values in existing records?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[SharonMee]

Hi Duane,

How do I paste append? What I did was to highlight the data in excel, then I clicked on the first cell in the access table, right clicked and I tried to paste but it's saying text too long or something like that.

Can you please provide me with the steps of pasting records into access. My access table has a auto number.

Thanks for your help.

SharonMee.

 

[TomCologne]

You didn't mention what error you're getting. I understand you're trying to import into an existing table and my guess is that the problem is either field size or data type or similar.

Excel allows a lot more than Access; You can write almost a novel into one cell or make an entry like 'n/a' in a date column cell.
The Access table will however only accept data matching the field settings. Text may not be too long, dates must be in valid date format(null is ok,'n/a' results in error), etc.

Either prepare data in the Xl sheet, eg using the "Replace" function

or

import the sheet to a new temporary table and make its contents compatible with your destination table within Access.

TomCologne

 

[SharonMee]

I have checked the field sizes e.t.c. to make sure they are the same (By the way, I always leave the field sizes to the default values).

I imported the excel table to a new access table and then tried to copy the table from there to my existing table in access, but it's only pasting 2 records and I have 1500 records or so. In the new access table I created, I selected all records,edit, copy all. In my desired table (the table only have 2 blank record fields to paste to) I selected selected all records and then paste.

I guess it's only pasting 2 records because there are only 2 blank records showing, how do increase the number of blank records so that it will accommodate all the records I need.

Thanks.

 

[dhookom]

Click on the record selector of the last record and then select Edit | Paste Append.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.