Solved

Run query in background

Posted on 2007-11-30
17
1,147 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 119

Expert Comment

by:Rey Obrero
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
 

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now