Having trouble with the append Query

[tsaiguy]

HI all, first time here.. just took on a new DBA position and trying to append data from an excel file to an exisiting table via access.

Here is some background... I'm running sql server and using access to "access" the databases. I am migrating 2 payroll databases and each database has unique ID's to match up (ie employee ID's) My problem is this...

When I run an append query to add the old payroll employee ID's to the new payroll system its gives me this error...

Microsoft Access set 0 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) due to lock violations, and (85) record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.

I've gone to microsofts website and they gave a fix for it to set the allowzerolength and se tot yes and the required property to No. that didn't work. Does anyone have any suggestions?

 

[GingerR]

I suggest that you use a MakeTable query, make a new table with the data, then look at the table structure/field properties the way that Access is seeing them, and compare these to the structure of the 'new' table you're trying to append to. Also, by doing this, you can look at all the data you're trying to get and see what is contained in each column and any adjustments you might need to make to 'fit' it into your new table.

To continue with this 'testing' process...make a copy of the 'new' table that you're having trouble appending to. Then write a test Append query to append the imported data from the make-table query to the test 'new' table. see which records append and which don't. OR another thing to try is to open the make-table table, select all (ctrl+a) then copy (ctrl+c), go to the test 'new' table, select the next new (blank) record and paste (ctrl+v). see what it says...if it will add some records and not others, or if it gives a more detailed error message.

Sorry I can't give you an easy answer. Importing data form various sources is fraught with frustration. Hopefully some of these tactics will help you fiddle around enough to sort it out. Let us know how it goes.

g

 

[tsaiguy]

Thanks g,

But the only problem is the Audit key that is set to Auto complete. I can't modify or screw up these data fields.

Is there something that I am doing wrong?

Tsai!

 

[BSman]

If you have an autonumber column you can run into problems. Even if there are no duplications in the values between the two data sources, you will still have problems because you can't directly load data into an autonumber column.

Also, although I doubt it is a problem in your case, be sure that none of your columns that are indexed require unique values (unless you are absolutely positive that the values in that column will always be unique).

Bringing the data into a new, temporary table, is a good first step because it will allow you to perform more analysis and testing to find out the cause of the loading problem.