Append Query problem

[Kathy]

I am trying to import simple name/address data from an Excel file into my
Access database. I worked in Excel to get the data into the right format
then imported the data into a new table in my Access database. I then made
sure each field in the Table matched the fields in the tables when I want to
append the data, same size field, all Text (although some imported as
numbers). Then I tried appending the records to the existing Access tables.
I get an error message for about half the records that they cannot be loaded
because there is a violation of a validation rule.

I have triple checked everything and can find no reason why some of the
records should load OK and some won't. They all came over from Excel OK
and all have the same format. None of the fields in the current Access
tables have a validation rule. I have done this before without problems.

Is there any way to determine which rule is being violated? Any place I
should be checking that I might have missed?? Kathy

 

[Jeff Boyce]

Kathy

Is there a chance that the number/text issue is bumping up against an Access
field type in one of your tables?

Can you isolate which table (and field) is having trouble? One approach
would be to run several "partial" appends, leaving out fields you suspect
until it breaks.

Have you "forced" the fields you receive from Excel to be the correct data
types? You can do this by importing to a temp table (accepts just what
comes in, interpreting the field type from the first few rows), then
creating a query that uses the "convert" functions (e.g., CInt() to convert
to Integer, CStr() to convert to String, CDate() to convert to Date/Time,
....).

Good luck

Jeff Boyce
<Access MVP>

 

[Van T. Dinh]

Few common problems you may want to check out:

* Trying to append duplicate values to uniquely-indexed Fields.

* Trying to append child Records without valid parent Records in a
One-to-Many relationship with Referential Integrity enforced.

 

[Jim/Chris]

Access uses the format of the excel worksheet based on the
first row. Put a dummy record in the Excel worksheet with
every column populated with correctly formatted data

Jim/Chris

 

[Kathy]

Thanks for everyone's help! I did try deleting each column to see what it
was that was causing the violation and to my surprize it was the columns
with no data or only an occasional piece of data. The data that was in them
was of the correct type, but Access (as Jim/Chris explained) apparently
didn't covert them since the first few records in that column were blank.
I had learned that Access did this when importing data, but didn't realize
that it didn't covert existing tables if there wasn't any data. I'll do
just as you suggested and move a record with data in all the columns to the
top of my lists. I am assuming this will solve the problem. Kathy

 

[Jeff Boyce]

Kathy

As suggested in-thread, you can also import as-is, then run a "converting"
query to append to a final table. This gives you a chance to do any
clean-up, parsing, and pre-processing.

Good luck

Jeff Boyce
<Access MVP>