Access transform x-tab SQL statement

[John Chee]

1) I'm using MS Office XP, VBA code in Access form.
2) I have 2 tables: A and B.

3) I run a [do while] loop to insert (insert into table....) 2000 records
to table A. Then I close my recordset (rs.close).

4) Then I create a 'transform' SQL like this:
lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
[InvDate]"
-- set RS=new adodb.recordset
--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic

Let's say the correct number of records is 60 records after running the
[transform] statement.

But when I place a Breakpoint at the line [lnRecordCount=RS.RecordCount],
the count is always less than 1, i.e. 60 -1 = 59 records.

I've done alot of testing on summary commands like transform, distinct,
group by. And I noticed that they (most of the time) return I record less.

However if I place a Msgbox between the two SQL statements like this, it
works:

###
a)
do while not rs.eof()
insert into tableA .....
(loop 2000 records)
loop
rs.close

'I need to add this message box.
'Only then I get the correct record count of 60.

Msgbox "Press any key to continue..."

lcSQL="transform sum(NetSales) as SumOfNetSales from tableA
group by DeptID, Dept, Pcls, Br order by DeptID, Dept, Pcls, Br pivot
[InvDate]"
-- set RS=new adodb.recordset
--Rs.open lcSQL, Conn, adOpenKeyset, adLockOptimistic
###

Is this a bug in VBA for Access? Is there a workaround when the user does
not have to see the Msgbox message?

 

[John Chee]

Please do not reply to this post because I'm getting response from
modulesdaovba group. Thanks.