Message from importing an excel file

[Hell-fire]

Hi,

I hope someone can help me with this. I created an excel file to match
tables in an access file. When I'm importing the data, at the last step I
get a message stating:

Microsoft Access was unable to append all the data to the table.

The contents of fields in 0 record(s) were deleted, and 102 record(s) were
lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the
field data types or the FieldSize property in the destination table.
*If records were lost, either the the records you pasted contain primary key
values that already exist in the destination table, or they violate
referential integrity rules for a relationship define between tables.

I'm using Access 2000 and Excel 2000 when I'm doing this. The fact is, all
the data that I'm importing, imports with no problem. Is there something I
need to do so this message doesn't come up when I click finish to import the
data?

Thank you

 

[Joan Wild]

It's a bit unclear. You say that all the data imports, but you can't import.

I'm guessing that you are importing directly from Excel into an existing table, and that is failing. If you import to a new table, it imports fine. So what is failing is the append to an existing table.

There are a number of things that would cause this:
-You have data that would create duplicates in a field that is set as the primary key (or has a unique index on it) - this may involve more than one field.
-You have set up a relationship with referential integrity with this table in the many side. You are appending data in this field that breaks the RI - i.e. this value doesn't exist in the table on the one side.
- you have a text field set to 'no' on the allow zero length, and the data you are appending has ZLS in it
- you have a validation rule in place that your data is violating.

Importing from Excel, I would bet on three - you think you have nulls when they are actually ZLS.

 

[Hell-fire]

Hi Joan,

Sorry if my post confused you. I can import the data with no problem.
Everything goes where its suppose to go, but whenever I keep importing new
data, that message keeps coming up.

I was just wondering if it was something in the Access field parameters or
the Excel cell parameters I need to adjust so this annoying message doesn't
appear. Thank you.

 

[Douglas J. Steele]

Does the new data you're trying to import also include the existing data? If
so, that's the cause of the problem: you're trying to insert the same data
into the table again, and you can't, because of primary key errors.

 

[Hell-fire]

Hi Douglas,

As for the data being imported, its always different. All the data imports
to the right fields, but I just keep getting that message. I even tried
importing to a duplicate table with no data in it and that message comes up.

I have changed some of the field parameters in Access and cell properties in
Excel and now I get "contents of fields in 1 records deleted, and 0 records
were lost".
No matter what I change I can't seem to get rid of that message. Thank you.

 

[Douglas J. Steele]

Are you doing this through code, or simply through the menu? If through
code, what does your code look like?

 

[Hell-fire]

Hi Douglas,

Sorry for my delay, I'm in Japan, so about 13 hours ahead of EST. I import
using the "Get external data" feature. The Excel file is on two sheets,
first sheet looks like a form and the second sheet holds the data from sheet
one.

I import the data from sheet two which has matching column names to the
field names in Access. Sometimes some columns has no data and are blank
when being imported. The formulas that are being used on sheet two are very
simple, especially since I'm not really familiar with Excel or even Access.

Thank you.

 

[Douglas J. Steele]

Sounds as though Access is misintepretting which rows of data in your
spreadsheet are supposed to be imported, and is trying to import blank rows
(and rejecting them as duplicates). You could define a range in Excel and
import the range, rather than simply the worksheet.