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.
MDauphinais1Asked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<Can I execute AFTER the form opens? Where would that go?>>

  Your already executing it after the form opens; your already in the load event.  But I know what you meant<g>.  

You can call it from the forms OnTimer event.  Default the forms timer to a nonzero value and then in the event, set the timer value to 0.  This will prevent the event from firing again.  Then call your check procedure.

  Another alternative would be to leave it in the forms OnLoad as it is now, but issue a Me.Repaint before calling it.  This would force Access  to completly draw the form before executing the query.  However the form will still pause when executing it.

  Best bet for what you want is probably the timer event.

JimD.
0
 
Rey Obrero (Capricorn1)Commented:
try placing

         doevents

after the line that runs the query
0
 
MDauphinais1Author Commented:
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
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

  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
0
 
MDauphinais1Author Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
 
MDauphinais1Author Commented:
Can I execute AFTER the form opens? Where would that go?
0
 
Gustav BrockCIOCommented:
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
0
 
MDauphinais1Author Commented:
cactus_data, can you please explain that a little bit more?
0
 
Gustav BrockConnect With a Mentor CIOCommented:
The form opens, then it assigns values to the unbound controls of which this new TextBox is one. When doing so, your function will be called and the update takes places.

Access is single threaded so the form will pause during the update but it will be visible and drawn in full.

/gustav
0
 
MDauphinais1Author Commented:
cactus_data, sorry, I meant explain about the DAO
0
 
Gustav BrockCIOCommented:
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
0
 
stevbeCommented:
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).OpenRecordset("qselCount1").Collect(0)
lng2 = DBEngine(0)(0).OpenRecordset("qselCount2").Collect(0)
If lng1 - lng2 ...


Steve
0
 
Gustav BrockCIOCommented:
> 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
0
 
stevbeCommented:
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
0
 
Gustav BrockCIOCommented:
Oh, certainly.

/gustav
0
 
Gustav BrockCIOCommented:
Did you find out?

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.