Not all records written using INSERT INTO (Excel/VBA to Acccess using ADO)

[mhirschman3114]

Newbie poster here. I have an Excel/VBA application that (a) writes records to a 'staging' table using INSERT INTO [it's a loop doing one record at a time from Excel sheet row to database table], (b) executes query "INSERT INTO MainTable SELECT * FROM StageTable" and then (c) executes query "DELETE * FROM StageTable". FYI the reason I'm using a staging table is because if/when there are multiple sheets of data, it's not guaranteed that they're all going to "work." So I "stage" it first, and if all goes well, I then write all to the main table. Basically I don't want any part of the Excel file if any of the data sheets fail to load to stage.

The problem I currently have is that not all records are being transferred from StageTable to MainTable, even though (a) I have verified that the data-types are all correct [I did an Access upload to verify that load will work if I do it directly from Access], (b) I have verified that all data really is going to the staging table as expected. I had initially gotten a "Can't update; <something> currently locked" error. But this was resolved by using a Wait(<3 seconds>) statement between the queries. (I hate that, by the way.)

At this point I have a Wait() statement between even the queries that ask for the number of records in tables, as these steps take place.

In any case, not all the records from a properly loaded staging table are being written using that INSERT INTO MainTable SELECT * FROM StageTable statement. There are no primary keys ... just pure data.

It SEEMS as if my queries are "interrupting" something going on within Access. I will try to verify by increasing the Wait() time but an actual understanding (and resolution) without relying on Wait() would be MUCH appreciated.

FYI, these queries are being passed using ADO to an Access2003 DB (and using Excel 2003). Thanks very much for any help.

[RunsWithScissors]

Can you post your code?


This kind of problem (Both the "Wait" issue, and the failure to INSERT all the reocrds), for me, has usually resulted from failing to release record locks in ADO.