Append Query Fails, MS Access 2000 bug?

[DB_Newbie2007]

I appear to be having a problem with an append query. I created a select
query, converted the query to a make table query. The query created the
table, NewTable, and inserted the data.

I then modified the query again to an append query. The data in NewTable was
deleted, then I ran the append query... everything ran fine.

I opened NewTable in Design View, saved the table (NO changes were made). I
re-ran the append query and it failed:

Microsoft Access can't append all the records in the append query.
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 validation rule violations....

I was trying to modify the table to add an index, but if the query fails
just for opening the table in design view and saving it, then the append
query fails, it is kind of difficult to add the index!

The only thing I can think if is Design View must be modifying the table
somehow? Putting some data fields into a mode other than what they were
before the Save?

Unfortunately, an upgrade to a newer version of MS Access is not possible,
as this version works with an application that requires MS Access 2000.

Any suggestions? Has anyone else experienced the same problem?

 

[Jeanette Cunningham]

Hi,
Comments in line

I opened NewTable in Design View, saved the table (NO changes were made).
I
re-ran the append query and it failed:

You ran the append query and added the data to the table.
Next step was to run the append query again.
The below error message is Access trying to tell you that the records are
already in the table
and they can't be added twice - presumably because you have a unique index
on at least one field.

Microsoft Access can't append all the records in the append query.
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 validation rule violations....

This is a good thing.
As a test, delete the data from NewTable and then check that the append
query works.

Jeanette Cunningham

 

[DB_Newbie2007]

I ran quite a few permutations before this post:

- Deleting the data and re-appending with append query (re-run 3 times,
without opening the table in Design View and Saving).: Data imported fine.
- Deleting the data, re-running append query (opening table in Design View
but NOT saving): Data imported fine.
- Created table (never opened in Design View), re-run append query a few
times: Data imported fine each time (lots of duplicates, of course!).
- Delete data, open table in Design View, then Save (no changes actually
made to table): Append query fails.
- Open table (with data in it) in Design View, then Save (no changes made to
table): Append query fails.

 

[DB_Newbie2007]

It did not appear to matter if there was data in the created table or not...
once the table was opened in Design View and a SAVE was done, the append
query stopped working.

 

[DB_Newbie2007]

Ok, I followed the instructions in Microsoft knowledge base 302504,
http://support.microsoft.com/kb/302504/en-us.

With the exception of two TEXT fields (which should never be empty), all of
the other TEXT fields were changed: the AllowZeroLength property was set to
YES.

This allowed me to run the append query without any problems. However, I
still think there is a bug when the append query works fine UNTIL the table
is opened in DESIGN VIEW, then SAVED. :S

 

[Jeanette Cunningham]

You certainly did a very thorough check up process.
Most of us would never go back and open a table in design view and then try
to save it if we didn't make any design changes.
Same applies in a production environment with your app, you wouldn't have
code that opened a table in design view and then try to save changes when no
changes were made. It doesn't make sense.

Jeanette Cunningham