Importing records into an existing table

[Jamescdawson]

I'm feeling a bit thick... I have an existing table "Customers" and have an excel spreadsheet of 100 new customers which I want to import/append onto my "Customers" table. I need the table to generate the customer ID key, (which will be used for linking to other tables) but I just can't get the records to import.



I use the Excel import button but I get the dialogue box telling me that Access failed to import

What am I missing or doing wrong ?

frustrated !

Jimbo

[June7]

Is the customer ID an autonumber type field?

You are using the import wizard?

Are the field names the same?

Is there more info in the error message?

If you want to provide files for analysis, follow instructions at bottom of my post.

[Jamescdawson]

Hi June7, Thanks for the prompt response...

Yes, the CustomerID is AutoNumber... (Why do you ask that?... I keep coming back to the problem being in the autonumbering of the destination field but no rationale)
Yes, I was using the import wizard
Yes, field names the same for the field in source and destination (I checked them to be so in an attempt to overcome issue)
Error message - See below....




I took another tack and imported the excel data into a new table and then tried to append the data using the Append query. Again, No records imported but this time the error message said it was because of "Validation Violations". I have checked every single field and there are no validation rules set.

Still frustrated

Jimbo

[June7]

I ask about the key to confirm this is not an imported value.

Are there any indexes set aside from the primary key field?

As enumerated in the error message, failure could be due to a number of causes. Would have to examine files.

[Jamescdawson]

June7

Thanks for that... I had the PostCode indexing. When I got your last reply, I removed the indexing from the Post Code and tried again. It failed again on Validation Violations.

I then removed any indexing from the source file - That failed too.

I've attached a db with the two tables (source and destination) test data

Can you append "sheet1" data to "Clients & Customers" ?

Jimbo

[June7]

Record 2 in Sheet1 has a space in the PostCode value.

Testing1 does not have fields except for the ID autonumber. APPEND requires fields to be in place. I added fields to Testing1. Moved the ID field to the end of the fields in table design view. Then this simple SQL works:

INSERT INTO Testing1 SELECT * FROM Sheet1;

Clients & Customers has more fields than the imported data. The common fields are not in the same order.

Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

I prefer Overlapping Windows option for db setup so I can see objects like tables and queries side by side.

[Jamescdawson]

Thanks for having a look.
Testing wasn't meant to have data/fields

The elements in question were Clients & Customers and Sheet1 contaed the data that I was trying to get into Clients & Customers.

I'll keep playing with it. Still can't see the reason for the Validation violation

Jimbo

[June7]

RefNumber field in Clients & Customers is required. This is the validation that is causing failure.

[Jamescdawson]

June7

You're a star. Well spotted.. I just don't know how I missed that !!! A whole day wasted faffing around with that.

Many Thanks

Best Regards

Jimbo