Infinite loops re-visited.

[David W. Fenton]

If access goes off and prefetches the next process thread for
forms, does it do this for queries too?

Totally separate issues. Jet/ACE is processing the query, while your
form has to be processed by both Access and Jet/ACE (unless the form
is unbound, in which case all the work is done by Access). In
general, Jet/ACE retrieves data asynchronously, i.e., using Rushmore
technology, you can start using the beginning of a retrieved
recordset before the last record in it has been retrieved.

If this is the case, then is there any way to
force a pause between these threads?

Your question is too abstract to be answered.

I suspect by the tickelish nature of
this bug, some place in the main stream code is being corrupted.
(I've seeen this in some of my C coding bugs.)

I used to run into weird problems in the cvrtNNN.dll, i.e,. the C
virtual runtime library, in Access 2000, particularly in the
situation where in a subform I referred to a field in the underlying
recordset of its parent form. In that case, it required a control on
the parent form with the underlying field as the controlsource.

Perhaps you've got a problem like that.

My experience is that Access 2000 introduced major bugs and
inconsistencies with previous versions in the way forms are bound to
recordsets. And there are issues with changes in the order of events
in later versions. I recently upgraded an A97 app to A2003, and
started having a regularly recurring bug on close of the app that
was caused by code in the OnUnload event of a subform that fired
before the parent form unloaded in A97 but only *after* the parent
form ceased to exist in A2003. I had to completely change my method
of accomplishing what I wanted to do (I was using a single form both
as subform and as standalone form, and in the latter context, it
needed to do things in the OnUnload event; if it was a subform, it
needed to ignore that event; I ended up changing to passing an
OpenArgs parameter to the form when opened standalone so I didn't
need to test if the form instance was a subform).

The culprit seems to be a make table query which is trying to copy
a very large, maybe too large, table.

What about replacing the MakeTable with a pre-existing empty table
and an APPEND query? Surely that will be faster? Perhaps not fast
enough, but it's worth a try, seems to me. Personally, I have
virtually no MakeTables in production apps. The only exception is
for archiving datasets, usually records that are getting exported
for some regular purpose. But those tables get made in a standalone
archive MDB that exists for the of storing those MakeTables. In
fact, nowadays, I would likely have an empty shell table, copy it to
the new table with the appropriate name and APPEND the current
archive to that.

But this all likely has little to do with your issue.

I would say that you might try something like:

- wrap your MakeTable/Append in a transaction,

- after it is launched, loop until there are records in the table.

There won't be any records until the transaction is committed, so
that would prevent the next step from executing until your table was
fully populated and ready to use.

Tom, your friend's filter program shows the
table to be too large if completely filled out. So I'm working on
trimming it down to size. Smaller assigned fields etc. But a
major re-design would a major effort and my bosses would be quite
upset over the delay. So this feature may go on indefinate hold.


Thank you all for all your help, and I'll keep you all advised of
my progress.

I've lost the original context, but if you're creating a temp table
to run a report, you might consider populating the table in the
OnOpen event of the report. In that case, I believe the report won't
display until the table is populated.

 

[Tom Wickerath]

Hi David,

I've lost the original context, but if you're creating a temp table...

I believe Chuck was talking about Allen Browne's "Database Issue Checker
Utility", which includes reporting on "Record too wide" (ie. the sizes of all
fields sum to more than 2000 characters).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[James A. Fortune]

I have triggered an infinite loop problem by adding a new function to an
existing program. The problem appears that the system goes into 100% usage
and becomes non responsive. Looking at the dask manager access is running
99%.

The new function is a macro which does the following:
Opens a form to select a record in a table ( client list)
makes a copy of the selected record ( copy of the client record)
opens a form to edit the newly copy record
When the edit form is closed, the newly copied-edited record is added to the
client list.

This macro set seems to run correctly in a standalone mode but when it was
added to the application macros, it causes the appllication to go into an
infinite loop. as described above.

My quesstion is: Now what can I do to determine the cause and fix it?

My system has 1 gig physical memory, but the system seems to wan to run in
1/2 gig.

"Infinite loops re-visited" indeed - what a title! In the past I have
noticed that DAO operations are especially prone to Access trying to be
helpful by continuing merrily along with the next set of instructions,
assuming that the asynchronous DAO operation will be finished in time
for when the VBA code needs the results. I rarely program macros except
to open a form or to run a procedure in an AutoExec macro, so I'll stick
with suggestions for VBA. Two common methods for dealing with such
helpfulness are:

1) Using an API function such as MilliSleep() to suspend running the
code until the DAO operation is finished:

'---module code
Public Declare Sub MilliSleep Lib "kernel32" Alias "Sleep" (ByVal
dwMilliseconds As Long)
'---end module code

Sample:

'Give the OS 2.0 seconds to save the changes
Call MilliSleep(2000)

2) Using a recordset's RecordsAffected or StillExecuting property to
know when an operation finishes:

strSQL = "SELECT Count(*) FROM tblAvailableItemNumbers;"
Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
MyRS.MoveLast
intCount = MyRS.RecordCount
If MyRS.RecordCount > 0 Then
'Clear the table used to populate the listbox
strSQL = "DELETE tblAvailableItemNumbers FROM tblAvailableItemNumbers;"
MyDB.Execute strSQL, dbFailOnError
If intCount > 1 Then
Do While MyDB.RecordsAffected = 0
DoEvents
Loop
End If
End If

The second option is better since the amount of time an operation takes
varies with things like computer speed, OS, network speed and version of
Access and might have to be increased in the future if an error occurs.
The Jet Database Engine Programmer's Guide recommends against having a
simple DoEvents within a loop, preferring the use of a counter and doing
a DoEvents every counter Mod n = 0.

James A. Fortune
(e-mail address removed)

 

[Chuckfonta]

Gentlemen, Greetings:

Taking Toms advice, I converted the demon macro into vba code, which is
included below. My comments are these:

The code is obviously machine generated code, especially with the
assignments to a variable, then executing the variable.

The code looks like a simple paraphrase of the macro code with the addition
of a wrapper.

If there is a multi thread hazard, I don't see how running this vba code is
different from the raw macro code, which is also included. (Excuse the white
space, I tried to simply copy the text and paset it but access would not
transfer the copy, so I used the documenter.

Not being a VBA programer, I'm quite puzzeled as to how to insert the
recomended delay patch. Where and how.

This problem seems to have taken on a life of its own, so here is more
information to keep it alive. If it would be of help, I could include the
documenter's analysis of the table I'm copying.


'------------------------------------------------------------
' copy_development_copy_edit_Tour_description
'
'------------------------------------------------------------
Function copy_development_copy_edit_Tour_description()
On Error GoTo copy_development_copy_edit_Tour_description_Err

DoCmd.OpenForm "Selected_Tour_Description_ID_Frm", acNormal, "", "",
acEdit, acDialog
DoCmd.OpenQuery "Copy_edit_TourdescriptionID edit Query", acViewNormal,
acEdit
DoCmd.close acQuery, "TourDescriptions_Tbl Query"
' edit copy
DoCmd.OpenForm "Copy change TourDescriptions_Tbl form", acNormal, "",
"", acEdit, acDialog
Beep
MsgBox "COPY COMPLETE", vbOKOnly, ""
End
' add copy to destination table
DoCmd.OpenQuery "add_Copy_edit_TourdescriptionID query", acViewNormal,
acEdit
DoCmd.close acQuery, "Copy_edit_TourdescriptionID edit Query"


copy_development_copy_edit_Tour_description_Exit:
Exit Function

copy_development_copy_edit_Tour_description_Err:
MsgBox Error$
Resume copy_development_copy_edit_Tour_description_Exit

End Function


C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 1
Properties
Container: Scripts DateCreated: 8/31/2009 7:16:42 PM
LastUpdated: 8/31/2009 7:16:42 PM Owner: admin
UserName: admin
Actions
Name Condition Action Argument Value
copy_edit_Tour_description
OpenForm Form Name: Selected_Tour_Description_ID_Frm










View: Form
Filter Name:
Where Condition:
Data Mode: Edit
Window Mode: Dialog
OpenQuery Query Name: Copy_edit_TourdescriptionID edit
Query









View: Datasheet
Data Mode: Edit
Close Object Type: Query











C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 2
Object Name: TourDescriptions_Tbl Query
Save: Prompt
OpenForm Form Name: Copy change TourDescriptions_Tbl
form









View: Form
Filter Name:
Where Condition:
Data Mode: Edit
Window Mode: Dialog
edit copy
MsgBox Message: COPY COMPLETE










Beep: Yes
Type: None
Title:
StopAllMacros











C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 3
OpenQuery Query Name: add_Copy_edit_TourdescriptionID
query









View: Datasheet
Data Mode: Edit
add copy to destination table
Close Object Type: Query










Object Name: Copy_edit_TourdescriptionID edit
Query
Save: Prompt

 

[Tom Wickerath]

Hi Chuck,

You can convert this function to a subroutine, unless you want it to return
a value, or you want to call it from a macro or directly from the Property
sheet for a control event (in which case it must remain a function).

Not being a VBA programer, I'm quite puzzeled as to how to insert the
recomended delay patch. Where and how.

Here is a re-write of your function, although it currently does not include
the idea of using a DoEvents every "counter Mod n = 0", as James had
mentioned in his reply:

"The Jet Database Engine Programmer's Guide recommends against having a
simple DoEvents within a loop, preferring the use of a counter and doing
a DoEvents every counter Mod n = 0."


Option Compare Database
Option Explicit

Public Sub RunUpdates()
On Error GoTo ProcError

Dim MyDB As DAO.Database
Dim strForm As String
Dim strQuery As String
Set MyDB = CurrentDb()

strForm = "Selected_Tour_Description_ID_Frm"

DoCmd.OpenForm strForm, View:=acNormal, _
DataMode:=acFormEdit, WindowMode:=acDialog

'Note: The next two lines of code assumes that the query
' "Copy_edit_TourdescriptionID edit Query" is considered
' an Action query (ie. Update, Delete, Append, Make Table, etc.
' but is *not* a SELECT query:

strQuery = "Copy_edit_TourdescriptionID edit Query"
MyDB.Execute strQuery, dbFailOnError

Do While MyDB.RecordsAffected = 0
DoEvents
Loop

'Close the first form now?
DoCmd.Close acForm, strForm

'Edit copy
strForm = "Copy change TourDescriptions_Tbl form"
DoCmd.OpenForm strForm, View:=acNormal, _
DataMode:=acFormEdit, WindowMode:=acDialog

'Add copy to destination table
strQuery = "add_Copy_edit_TourdescriptionID query"
MyDB.Execute strQuery, dbFailOnError

Do While MyDB.RecordsAffected = 0
DoEvents
Loop

DoCmd.Close acForm, strForm

MsgBox "The Copy Operations Have Completed.", _
vbOKOnly + vbInformation, "Copy Complete..."

ExitProc:
'Cleanup
Set MyDB = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in RunUpdates Procedure..."
Resume ExitProc
End Sub

'******End Code*************************

A couple of suggestions for you:

1.) Do not use special characters, including spaces, in the name of anything
that you assign a name to within Access (queries, forms, controls on
forms/reports, etc.)

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

2.) Consider implementing a naming convention. For example, it is helpful if
one can tell from the name of a query the type of query.

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
http://www.xoc.net/standards/rvbanc.asp#Access

Using a Naming Convention
http://msdn2.microsoft.com/en-us/library/aa164529(office.10).aspx

3.) Make sure that you are not using any reserved words

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

4.) When you create a new module, if you do not see those two very important
words "Option Explicit" as the second line of code, then you should configure
your Visual Basic Editor (VBE) by selecting the option "Always require
variable declaration".

Always Use Option Explicit
http://www.access.qbuilt.com/html/gem_tips.html#VBEOptions


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

Gentlemen, Greetings:

Taking Toms advice, I converted the demon macro into vba code, which is
included below. My comments are these:

The code is obviously machine generated code, especially with the
assignments to a variable, then executing the variable.

The code looks like a simple paraphrase of the macro code with the addition
of a wrapper.

If there is a multi thread hazard, I don't see how running this vba code is
different from the raw macro code, which is also included. (Excuse the white
space, I tried to simply copy the text and paset it but access would not
transfer the copy, so I used the documenter.

Not being a VBA programer, I'm quite puzzeled as to how to insert the
recomended delay patch. Where and how.

This problem seems to have taken on a life of its own, so here is more
information to keep it alive. If it would be of help, I could include the
documenter's analysis of the table I'm copying.


'------------------------------------------------------------
' copy_development_copy_edit_Tour_description
'
'------------------------------------------------------------
Function copy_development_copy_edit_Tour_description()
On Error GoTo copy_development_copy_edit_Tour_description_Err

DoCmd.OpenForm "Selected_Tour_Description_ID_Frm", acNormal, "", "",
acEdit, acDialog
DoCmd.OpenQuery "Copy_edit_TourdescriptionID edit Query", acViewNormal,
acEdit
DoCmd.close acQuery, "TourDescriptions_Tbl Query"
' edit copy
DoCmd.OpenForm "Copy change TourDescriptions_Tbl form", acNormal, "",
"", acEdit, acDialog
Beep
MsgBox "COPY COMPLETE", vbOKOnly, ""
End
' add copy to destination table
DoCmd.OpenQuery "add_Copy_edit_TourdescriptionID query", acViewNormal,
acEdit
DoCmd.close acQuery, "Copy_edit_TourdescriptionID edit Query"


copy_development_copy_edit_Tour_description_Exit:
Exit Function

copy_development_copy_edit_Tour_description_Err:
MsgBox Error$
Resume copy_development_copy_edit_Tour_description_Exit

End Function


C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 1
Properties
Container: Scripts DateCreated: 8/31/2009 7:16:42 PM
LastUpdated: 8/31/2009 7:16:42 PM Owner: admin
UserName: admin
Actions
Name Condition Action Argument Value
copy_edit_Tour_description
OpenForm Form Name: Selected_Tour_Description_ID_Frm


View: Form
Filter Name:
Where Condition:
Data Mode: Edit
Window Mode: Dialog
OpenQuery Query Name: Copy_edit_TourdescriptionID edit
Query


View: Datasheet
Data Mode: Edit
Close Object Type: Query


C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 2
Object Name: TourDescriptions_Tbl Query
Save: Prompt
OpenForm Form Name: Copy change TourDescriptions_Tbl
form


View: Form
Filter Name:
Where Condition:
Data Mode: Edit
Window Mode: Dialog
edit copy
MsgBox Message: COPY COMPLETE


Beep: Yes
Type: None
Title:
StopAllMacros


C:\Documents and Settings\callahan Immage\My Documents\august 21\Split
Sunday, September 06, 2009
Travel V1.02.accdb
Macro: copy_development Page: 3
OpenQuery Query Name: add_Copy_edit_TourdescriptionID
query


View: Datasheet
Data Mode: Edit
add copy to destination table
Close Object Type: Query


Object Name: Copy_edit_TourdescriptionID edit
Query
Save: Prompt

 

[James A. Fortune]

'Note: The next two lines of code assumes that the query
' "Copy_edit_TourdescriptionID edit Query" is considered
' an Action query (ie. Update, Delete, Append, Make Table, etc.
' but is *not* a SELECT query:

strQuery = "Copy_edit_TourdescriptionID edit Query"
MyDB.Execute strQuery, dbFailOnError

Do While MyDB.RecordsAffected = 0
DoEvents
Loop

If you use that code, test to be sure that the action query won't cause
an infinite loop if, say, a delete query is run on a table that doesn't
contain any records and causes MyDB.RecordsAffected to stay at 0.

Here is a module function I use to see if a table has any records:

Public Function DHasRecords(strSQL As String) As Boolean
Dim MyDB As Database
Dim CountRS As Recordset

DHasRecords = False
Set MyDB = CurrentDb
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
If CountRS.RecordCount > 0 Then
DHasRecords = True
End If
CountRS.Close
Set CountRS = Nothing
Set MyDB = Nothing
End Function

Sample usage:

strSQL = "SELECT * FROM tblReuse;"
If DHasRecords(strSQL) Then
'Execute the delete query
strSQL = "DELETE tblReuse FROM tblReuse;"
MyDB.Execute strSQL, dbFailOnError
Do While MyDB.RecordsAffected = 0
DoEvents
Millisleep(100) 'Assumes API function declaration is in a module
Loop
End If

Note: Having a primary key on tblReuse should cause the DHasRecords()
function to run quite rapidly.

James A. Fortune
(e-mail address removed)

 

[Tom Wickerath]

Hi James,

If you use that code, test to be sure that the action query won't cause
an infinite loop if, say, a delete query is run on a table that doesn't
contain any records and causes MyDB.RecordsAffected to stay at 0.

Good point! In that case, if the query is a delete query, one can simply
remove the delay loop code. Probably better to replace a named delete query
with the equivalent SQL statement, so that it is patently obvious what the
line of code is intended to do:

MyDB.Execute "DELETE * FROM TableName", dbFailOnError




Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[James A. Fortune]

Hi James,




Good point! In that case, if the query is a delete query, one can simply
remove the delay loop code. Probably better to replace a named delete query
with the equivalent SQL statement, so that it is patently obvious what the
line of code is intended to do:

MyDB.Execute "DELETE * FROM TableName", dbFailOnError




Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

Showing the SQL is definitely helpful, but in the case you present, note
that if the number of records gets quite large, the delete query takes
much longer to execute. I've had situations where the code ran great
until the number of records got to a certain point where the table
wasn't totally empty before I started adding records to it. Ouch! So I
find that the delay loop code is vital for my purposes.

James A. Fortune
(e-mail address removed)

 

[David W. Fenton]

strQuery = "Copy_edit_TourdescriptionID edit Query"
MyDB.Execute strQuery, dbFailOnError

Do While MyDB.RecordsAffected = 0
DoEvents
Loop

This looks like nonsense code to me.

It assumes that the .Execute command executes asynchronously and
that VBA code after it runs before the .Execute has completed.

I think this is a completely unwarranted assumption. I have never at
any point written any code that did not assume that .RecordsAffected
was not completely accurate on the line immediately following the
..Execute command. I have never seen a case in which it was *not*
accurate, but your code assumes it gets updated as the delete is
processed.

..RecordsAffected is not like .Recordcount for a recordset, so far as
I'm aware.

I think that if you put a counter inside that loop, it would never
increment.

My reason for that is that so far as I'm aware, an .Execute
statement uses implicit transactions, and there's no point where
there's a partial operation. That is, the deletion is done in the
temp file and then applied all at once to the real data.

Indeed, in the Jet Database Engine Programmers Guide, p. 96, I see
this:

Certain properties [of a QueryDef], such as RecordsAffected, have
a value only after the QueryDef object is executed.

Of course, that's the .RecordsAffected property of a QueryDef, not
of a database object., but 3 pages later, I read this:

...when you use a temporary QueryDef object, you can't take
advantage of certain properties of a permanent QueryDef object,
such as the RecordsAffected property. To work around this
problem, you can use the RecordsAffected property on the Database
object.

That seems to suggest that the value you get for RecordsAffected
will be equivalent to the one you'd get for a saved QueryDef, and
that suggests that the caveat quoted above, from p. 96, would apply
here.

But I see that RecordsAffected, whether on a database object or on a
QueryDef, returns 0 before anything has been executed, so it seems
there *is* a value, just that the value is 0.

On the other hand, the documentation is for Jet 3.5 and we're all
using Jet 4.0 or higher now.

I honestly don't believe from experience or from any of my reading
or from any example code I've seen from Microsoft that
RecordsAffected is ever inaccurate. I don't think that loop will
have any effect at all.

 

[James A. Fortune]

It assumes that the .Execute command executes asynchronously and
that VBA code after it runs before the .Execute has completed.

I think this is a completely unwarranted assumption. I have never at
any point written any code that did not assume that .RecordsAffected
was not completely accurate on the line immediately following the
.Execute command. I have never seen a case in which it was *not*
accurate, but your code assumes it gets updated as the delete is
processed.

I'm still forced to support the use of Jet 3.5 so I can only speak for
that situation when I say that as far as I can tell, the VBA code after
it sometimes runs before the .Execute has completed. Until I know that
later versions of VBA aren't as 'helpful' in interacting with Jet,
keeping such code around is only reasonable.

James A. Fortune
(e-mail address removed)

 

[Chuckfonta]

Geltlemen!
I wish to thank you all for your support. I have come to the conclusion
that the problem is one which I can not solve with my experience, even with
all of your help.

Not being famalior with Visual basic for applications, or the Jet engine,
and other tools or compilers mentioned I am conceeding defeat. For your
interest here is the last straw which broke this camel's back.

I took the table I'm trying to copy out of my application and put it into a
blank data base. Then I re-made the make table query the same as the one in
the original application. Ran it both as a stand alone query and as part of
a select, copy, edit macro at full speed. Low and behold the macro works
and I can exit this simple application.

This has convinced me that there is something in my (novice) coding style,
either size, sequence, etc. which has uncovered a subtle bug in access. For
now my client will have to do with out this option, as it has become a black
hole for time.

Thanks again for all your help! And if I ever solve the problem I'll post
the soloution on this thread.

Chuck

 

[Tom Wickerath]

Hi Chuck,

I took the table I'm trying to copy out of my application and put it into a
blank data base. Then I re-made the make table query the same as the one in
the original application. Ran it both as a stand alone query and as part of
a select, copy, edit macro at full speed. Low and behold the macro works
and I can exit this simple application.

Well, who knows....you may have fixed some subtle corruption in your
database simply by importing the objects into a new database container (ie. a
new .mdb or .accdb file). This is something that all experienced Access
developers must do, from time-to-time, to solve corruption problems. I cover
this technique starting on the lower half of page 3, of a Word document that
I call Access Links. You are welcome to download a zipped copy from my web
site:

http://www.accessmvp.com/TWickerath/

I hate to see anyone give up. I can help you convert your macro to VBA code
(no charge), if you are willing to send me a compacted and zipped copy of
your database by private e-mail. It may or may not fix the issue.

If you are interested, send me a private e-mail message with a valid reply-to
address. My e-mail address is available at the bottom of this page:

http://www.access.qbuilt.com/html/expert_contributors.html#TomW

Scroll down past the two pictures, to the bottom of the page, where you
should
see a clickable link. Please do not post your e-mail address (or mine) to a
newsgroup reply. Doing so will only attract the unwanted attention of
spammers.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[David W. Fenton]

I'm still forced to support the use of Jet 3.5 so I can only speak
for that situation when I say that as far as I can tell, the VBA
code after it sometimes runs before the .Execute has completed.
Until I know that later versions of VBA aren't as 'helpful' in
interacting with Jet, keeping such code around is only reasonable.

I don't believe .Execute executed asynchronously in any version of
Access that I've ever used (starting with 2, and Jet 2.5).

I've certainly never written a line of code that didn't assume so,
and never been surprised by the results.

There is nothing in the Jet 3.5 Database Engine Programmers Guide
that suggests asynchronous execution, nor that there is every a time
when the .RecordsAffected property is available *after* an Execute
and will not be accurate.

 

[James A. Fortune]

I don't believe .Execute executed asynchronously in any version of
Access that I've ever used (starting with 2, and Jet 2.5).

I've certainly never written a line of code that didn't assume so,
and never been surprised by the results.

There is nothing in the Jet 3.5 Database Engine Programmers Guide
that suggests asynchronous execution, nor that there is every a time
when the .RecordsAffected property is available *after* an Execute
and will not be accurate.

Despite what you read, for Jet 3.5, my guess is that you were lucky not
to run queries that executed long enough to dispel your incorrect
assumption. Several others reported timing problems in CDMA associated
with the same problem. Asynchronous DAO execution is the only
reasonable explanation, but if you can come up with a better reason, I'd
like to hear it.

James A. Fortune
(e-mail address removed)