Access Data Errors When Running, But Not in Debug Mode (Synchronous or Parallel??) (1 Viewer)

dasmit

New member
Local time
Today, 09:03
Joined
Nov 5, 2019
Messages
2
Hello--

I am not an experienced programmer, but do so programming on the side. The party I am assisting has an Access database split into a front end and back end, connected (via ODBC link) to SQL tables in different SQL databases.

The issue: When running the code for certain processing, data that I download from SQL tables into local tables is corrupted -- the data is just incorrect and may, for example, include an extra record. But, when I step through the code in debug mode, the data downloads perfectly and without an extra data or errors. (Specifically, the code does a bunch of things, including calling an API that posts a transaction and then returns some data. When running, the returned data seems to include additional entries that they get logged. If I delete the log and look up the data in the original SQL tables and redownload, the information doesn't include the extraneous entries. Also, if I step through the code, I have no errors/extraneous data.)

I am assuming this is something with the way the queries are running with the SQL database providing data (since Access VBA is synchronous I believe), but don't know how to force my code to make sure the data that is returned is correct and act as if I am stepping through the code in debug.

Of course, it is possible that this error is causing these type of problems that someone in this group may know about.

Any help, suggestions, or thoughts would be greatly appreciated.

Thank you!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:03
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! You are correct that Access is single-threaded, so it's quite possible there is some timing issue going on that is not evident when you step through the code because you are introducing a time delay when you do that. If so, you might try inserting some time delays in strategic places in your code to see if it will help.
 

dasmit

New member
Local time
Today, 09:03
Joined
Nov 5, 2019
Messages
2
Thanks theDBguy. I have tried at times to add sleep/doevents/etc. at times during places in the code, but haven't found the right set to avoid problems at all times that don't slow things down too much and avoid errors. That is why I was wondering whether there is something weird/special with SQL connections that may be causing the problems and that someone has another suggestion that may help.

Thanks again!
 

Micron

AWF VIP
Local time
Today, 12:03
Joined
Oct 20, 2018
Messages
3,476
I use a function that loops for the period of time that I specify and because it's in a standard module, I can call it from anywhere. Most likely you'd need a pause after a line that executes an action query or does some other task that may require time for it to complete. I've been playing with integer (whole seconds) and other types such as Single and Double to pass fractions of a second to it, so here's both versions. The call would be like Pause (2) for 2 seconds.
Code:
'Public Function Pause(intSecs As Integer)
'Dim Start As Variant
'Start = Timer
'Do While Timer < Start + intSecs
'Debug.Print Timer
'    DoEvents
'Loop
 'Debug.Print intSecs
'End Function
 
Public Function Pause(interval As Variant)
Dim Start As Double
Start = Timer
Do While Timer < Start + interval
Debug.Print Timer
Loop
End Function
I was playing around with Do Events to see if it had any impact but IIRC it does not, so you should be able to remove that line and the debug that I was monitoring with.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 16:03
Joined
Feb 19, 2013
Messages
16,553
Just to add a comment about doevents and sql server - sql server events occur on the server and not on the local machine - and doevents refers to events on the local machine
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:03
Joined
Feb 28, 2001
Messages
26,999
If the query in question was coded appropriately, Access releases control and can execute the next code steps while the query is still running. In such cases, you have to check back on that query's status. Unfortunately, I have never had to do this. I've only read about it. If the queries are set up to potentially be asynchronous, that could be the cause of your problem. You would need to show the code used to set up the query and execute it so that the SQL mavens here would be able to advise you better.
 

Users who are viewing this thread

Top Bottom