Solved

Run query in background

Posted on 2007-11-30
17
1,186 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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 50

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 50

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 50

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 20384541
Oh, certainly.

/gustav
0
 
LVL 50

Expert Comment

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

/gustav
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

726 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