MDauphinais1
asked on
Run query in background
I have a form that's used as a main switchboard when the user launches the MDB. One thing that happens when the form opens is a query is run and if the result is 1 then a message box is visible on the form, if the return is 0 then it isn't. The problem is that this query takes a little while to run and so it stops the form from loading until it is finished. Is there anyway to run the query in the background so that the form can load as normal and then when the query is finished just display or don't display the message?
The query is a stored procedure being launched with a Macro.
The query is a stored procedure being launched with a Macro.
ASKER
This is what I have:
It is still waiting for the query to finish before loading the form.
Private Sub Form_Load()
TTCheck
DoEvents
End Sub
Private Function TTCheck()
Dim stDoc35Name As String
stDoc35Name = "EMCombinedPersonalTT"
DoCmd.RunMacro stDoc35Name
If DCount("ID", "TTCounttmp") - DCount("AutoNum", "Time Tracker SB Count Union") > 0 Then
Me.Command333.Visible = True
Me.TTCount.Visible = True
Else
Me.Command333.Visible = False
Me.TTCount.Visible = False
End If
End Function
It is still waiting for the query to finish before loading the form.
Private Sub Form_Load()
TTCheck
DoEvents
End Sub
Private Function TTCheck()
Dim stDoc35Name As String
stDoc35Name = "EMCombinedPersonalTT"
DoCmd.RunMacro stDoc35Name
If DCount("ID", "TTCounttmp") - DCount("AutoNum", "Time Tracker SB Count Union") > 0 Then
Me.Command333.Visible = True
Me.TTCount.Visible = True
Else
Me.Command333.Visible = False
Me.TTCount.Visible = False
End If
End Function
No, not really other then through an ODBC Direct Workspace. All queries in Access are synchronus. ODBC Direct is the only way to run an asynchronus query.
If you don't want to go the ODBC Direct route, then as an alternative, I would either:
A. Work on the queries performance.
B. Execute the query before the form opens.
JimD
ASKER
I should note that the query that is being run is not associated with the record source of the form. Is is just being executed so a DCount can be completed.
<<I should note that the query that is being run is not associated with the record source of the form. Is is just being executed so a DCount can be completed.>>
Doesn't make any difference in this case.
JimD.
Doesn't make any difference in this case.
JimD.
ASKER
Can I execute AFTER the form opens? Where would that go?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1. Remove the code from the OnLoad event.
2. Create an unbound TextBox on the form and assign to its ControlSource:
=TTCheck()
However, running a macro which runs a query is about the slowest possible way to update something. Consider rewriting this to be done directly from code using DAO - it will run a magnitude faster.
/gustav
2. Create an unbound TextBox on the form and assign to its ControlSource:
=TTCheck()
However, running a macro which runs a query is about the slowest possible way to update something. Consider rewriting this to be done directly from code using DAO - it will run a magnitude faster.
/gustav
ASKER
cactus_data, can you please explain that a little bit more?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
cactus_data, sorry, I meant explain about the DAO
That is to replace the query with VBA code that manipulates the tables like the query does. For a simple query it is easy, for a complicated query it may be too difficult or time consuming to write.
/gustav
/gustav
Access has internal optimizations for DCOunt if you use * instead of a field name ... might help some ...
If DCount("*", "TTCounttmp") - DCount("*", "Time Tracker SB Count Union")
but if you make the queries themselves do the Count then you could just grab the values and do the math ... a saved query almost always beats running code ...
make a new query ... add the TTCounttmp query and make 1 field be Count *, call it qselCount1,
Dim lng1 As Long
lng1 = DBEngine(0)(0).OpenRecords
lng2 = DBEngine(0)(0).OpenRecords
If lng1 - lng2 ...
Steve
If DCount("*", "TTCounttmp") - DCount("*", "Time Tracker SB Count Union")
but if you make the queries themselves do the Count then you could just grab the values and do the math ... a saved query almost always beats running code ...
make a new query ... add the TTCounttmp query and make 1 field be Count *, call it qselCount1,
Dim lng1 As Long
lng1 = DBEngine(0)(0).OpenRecords
lng2 = DBEngine(0)(0).OpenRecords
If lng1 - lng2 ...
Steve
> a saved query almost always beats running code ...
That depends. For example, if you run a loop to append records, usually using DAO or even ADO directly on an open recordset is much faster. Often you can tell if code is written by an Access programmer or a VB or ASP programmer - the last two will typically put an SQL string to execute within the loop which is very slow.
/gustav
That depends. For example, if you run a loop to append records, usually using DAO or even ADO directly on an open recordset is much faster. Often you can tell if code is written by an Access programmer or a VB or ASP programmer - the last two will typically put an SQL string to execute within the loop which is very slow.
/gustav
gustav ... given the context of this question ... getting a Count ... I would be willing to bet that a saved query will execute faster than anything the best programmer could write in a loop.
Steve
Steve
Oh, certainly.
/gustav
/gustav
Did you find out?
/gustav
/gustav
doevents
after the line that runs the query