To whom it may concern,

Environment:
I am working with MS Access 2003, database format of 2000.

Issue:
Source table is in external database, linked to processing database.  When using "Append Table" query, some of the data is missing.  When using "Create Table" query, all data is present.

Columns contains both nulls and date/time values.  Missing data is only for SOME of the records, not all.  For example, column F, row 186, 200 and 225 all have valid date/time value.  "Append Table" SQL copies only row 186 column F; "Create Table" SQL has values in all rows, column F.

Both SQL statements are copying data, there is no processing of the data prior to inserting into the desired output table.

Troubleshooting:
- Compact/Repair of processing database
- Re-establish data link
- Import source table into processing database

Results:
When using delete * from table, then applying "Append Table" SQL, data is missing.

When using drop table, then applying "Create Table" SQL, data is there.

Why is this happening?

Thanks,
Tim Tate
 

---

Answer from Richard Rost:

Wow… that's interesting. I've never seen that happen before. Do you have some kind of data validation or indexing in place that's preventing Access from appending records to a table that already exists? That's about the only thing I can think of. Without looking at a copy of your database, I have no other suggestions. Look for indexing, data validation, or other conflicts at the table level.