Import problems from Excel 2000 - Access 2000

[Chappy]

Hi,

I have recently started using a database and are looking to automate some
cumbersome processes. Staff have been entering a date field via a form but
due to the volume this is very time consuming.

I noticed that the relevant table had no primary key and had to create an
autonumber field to assist in importing this data. However when I try to
import the data from an Excel spreadsheet I receive an error message:

XXX was unable to append all the data to the table.
The contents of fields in 0 records were deleted and 3 records were lost due
to key violations.

The excel spreadsheet only contains 2 columns, one for the the primary key,
the other with a date. The date field is the one I wish to have the
information inserted into.

Any ideas?

Both are Office 2000 applications.

Cheers,
Chaps

 

[Jeanette Cunningham]

Chappy,
the error message

The contents of fields in 0 records were deleted and 3 records were lost
due
to key violations.

suggests that the 3 lost records had duplicates in the primary key field.

From that I assume that you are importing into an existing table in access.
If the primary key field has a unique index, it would prevent importing
duplicates in that field.

What happens if you import into a temporary table by selecting new table
from the import wizard?
Can you see 3 duplicates for the primary key field?

Jeanette Cunningham

 

[Chappy]

Hi Jeanette,

Many thanks for your quick reply.

I apologise, I should have been clearer.

I am importing into an existing table in Access. I want the date information
I am importing to be placed under the relevant date field I have in the
table. There is an existing record in the field that the primary key refers
to but I want to use that to reference which recod the date goes into.

Eg, the following I wish to import is in an excel spreadsheet:

PK Date
89392 28/02/08
89201 29/02/08

In the access table I already have a record with the PK 89392 and 89201 but
the Date field is blank. So I wish to have this date information placed in
the date field corresponding to the relevant record.

I though Access could do that or is that not possible???

Thanks
Chappy.

 

[Jeanette Cunningham]

Doing that is a horse of a different colour.
What you require is an update query, not an append.
Importing into the existing table is the same as appending.
An update query is used to update one field using a matching field from one
table to another.

Hint - make a backup of your table before you run this query.
If you link your database to the excel spreadsheet, try creating an append
query by joining the spreadsheet to the existing table on the PK field in
query design view.
I haven't tried this myself using an excel spreadsheet- but it might work.
Change the query to an update query and in the Update To row, under
TheDateField, put the table name and field name from the spreadsheet,
In the Criteria: row, under TheDateField, put Is Not Null.
Run the query.

Jeanette Cunningham