Inconsistent append query execution

I have the following append (insert) query saved in an Access 2002 sp3 MDB
using the 2000 file format. I have a VB6 application that uses DAO 3.6 to
run this query. For some reason, even though the source table may have over
500 rows, this query will only copy 8 rows or sometimes none or sometimes
all. Here’s the query:


INSERT INTO importFromExcel_regSHO_LetterMMDDYY ( Security, Participant,
AccountID, AtRisk, DateStart, DateBuyIn, Age, Ticker, [MLPro/PAX], Name, IPS,
PierceAccountID, Comments )
SELECT i.[Security Number], i.participant, i.[Account Number], i.[At Risk],
i.[Start Date], i.[buying date], i.[Days Aged], i.Ticker, i.MLProAcct,
i.[Account Name], i.[IPS Symbol], i.Pierceaccount, i.Comments
FROM importFromCSV_regSHO_LetterMMDDYY AS i;


Notes/questions:
I’m not receiving any errors.
It works if I step through my code in VB.
It works if I call it manually in Access.
This is one query called in the middle of a batch of queries.
Is there a better way to call this query (than DAO)?

I did come up with a solution (hack) when I took a closer look at the
problem. It seems that following an append operation, Access may not be able
to make all the newly added records immediately available. As a result, any
operations after an append may appear to operate on none or only a subset of
records in the table. An example may help:

1. Empty table1
2. Empty table2
3. Import a spreadsheet into table1
4. Append table2 from table1
5. Read table2

In this example, there are two possible places where Access may not be able
to return (read) all the newly appended records:

1. In step 4, table2 may only end up with a subset of the records actually
imported from the spreadsheet.
2. In step 5, I may only see a subset of the records appended from table1.

In each of these cases, I know what the expected record count should be, so
I keep manually counting the records until it equals my expected record count
(or it hits my retry max (10,000)). When I hit my expected record count, I
continue working.

Another wrinkle is the data access method (I may not be using the correct
terminology) used to append records. If I use
Access.Application.DoCmd.TransferSpreadsheet to import records, the Recordset
object I use for counting has to be created from
Access.Application.CurrentDb.OpenRecordset. If I use DAO to run an append
action query, I need to create the counting Recordset using DAO.

Is there any documentation about this behavior?

If anyone has a better way, I’d love to hear about it.


"ashwin" wrote:

> I have the following append (insert) query saved in an Access 2002 sp3 MDB
> using the 2000 file format. I have a VB6 application that uses DAO 3.6 to
> run this query. For some reason, even though the source table may have over
> 500 rows, this query will only copy 8 rows or sometimes none or sometimes
> all. Here’s the query:
>
>
> INSERT INTO importFromExcel_regSHO_LetterMMDDYY ( Security, Participant,
> AccountID, AtRisk, DateStart, DateBuyIn, Age, Ticker, [MLPro/PAX], Name, IPS,
> PierceAccountID, Comments )
> SELECT i.[Security Number], i.participant, i.[Account Number], i.[At Risk],
> i.[Start Date], i.[buying date], i.[Days Aged], i.Ticker, i.MLProAcct,
> i.[Account Name], i.[IPS Symbol], i.Pierceaccount, i.Comments
> FROM importFromCSV_regSHO_LetterMMDDYY AS i;
>
>
> Notes/questions:
> I’m not receiving any errors.
> It works if I step through my code in VB.
> It works if I call it manually in Access.
> This is one query called in the middle of a batch of queries.
> Is there a better way to call this query (than DAO)?
>
>