Solved

Run query in background

Posted on 2007-11-30
17
1,178 Views
Last Modified: 2008-05-20
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.
0
Comment
Question by:MDauphinais1
  • 6
  • 5
  • 3
  • +2
17 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20381428
try placing

         doevents

after the line that runs the query
0
 

Author Comment

by:MDauphinais1
ID: 20381607
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
 
LVL 57
ID: 20381622

  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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:MDauphinais1
ID: 20381662
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
 
LVL 57
ID: 20381706
<<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
 

Author Comment

by:MDauphinais1
ID: 20381715
Can I execute AFTER the form opens? Where would that go?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 20381807
<<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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20382290
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
 

Author Comment

by:MDauphinais1
ID: 20382409
cactus_data, can you please explain that a little bit more?
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 total points
ID: 20382482
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
 

Author Comment

by:MDauphinais1
ID: 20382521
cactus_data, sorry, I meant explain about the DAO
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20382609
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
 
LVL 39

Expert Comment

by:stevbe
ID: 20383943
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20384048
> 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
 
LVL 39

Expert Comment

by:stevbe
ID: 20384509
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20384541
Oh, certainly.

/gustav
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 20704986
Did you find out?

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question