I have some pretty complex queries that run with the docmd.runSQL method, and some take up to 30 seconds to complete. How can I put something nice on the screen to tell the user it is still working, rather than just having to watch the blue bar in the status bar, or wait for the Egg Timer to go?
Access has a problem doing two things at once. I display a label to alert the user that something is processing...please wait. You could customize the status bar text to display something as well. The trick to get the label to appear before your query starts is that you have to set a brief pause in between the call to display the label or update the label caption and before the query is called. Like this...
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause
End Function
The trick to get the label to appear before your query starts is that you have to set a brief pause in between the call to display the label or update the label caption
What I do create another form that says "Loading..." or "Please wait..." or even "Please wait while loading.." I make the form sort of look like a message box, but more colorful. I strip it of all the record navigation and scrollbar, I change the border property to thin. Take away the min/max and so on.
Then before I run the query I call:
DoCmd.OpenForm "Form Name", acNormal, , , acFormEdit, acWindowNormal
Forms("Form Name").Repaint
After the query do one of two things, close the form DoCmd.Close acForm, or change it's visibility to false Forms("Form Name").Visibility = False.
I normally leave the form open and change it's visibility..so if I access the form again, all I have to do is change visibility and then repaint. Closing and opening require more time and slows down the database.
Technically, I should just open the form in the background when the Db loads and change the form's visibility throughout the sub or function and just close the form on exit (this would be better.. but for me it's not guaranteed that I'll run the query or not, so I don't).
Thanks for the input guys...I tried the using a 'Please wait...' type form, and seemed to work, until one of the queries was using the form data within itself...ie. the query contained me!fieldname references, and 'me' was now a dummy 'Please wait' form with no fields...
Any other thoughts? I suppose I could make my references more specific, by using formname!field instead of me!field, but my sql is pretty big, and I dont relish changing it all.
I have some queries that run from a General Module also, and with no form loaded except the switchboard...I guess I could change the title Caption on the switchboard to say please wait ....maybe...
I suppose I could make my references more specific, by using formname!field instead of me!field, but my sql is pretty big, and I dont relish changing it all.
Sounds like you have a pretty bad case of laziness:
1) Copy the SQL
2) Open Notepad..press ctrl+v
3) Press ctrl+h; | Find: Me. || Replace with: FORMS![Form Name] |
4) Press ctrl+a then ctrl+c
5) Put back where you pulled the SQL from
I'm a big believer in using "Me" sparingly. Access has enough troubles as it is =)
I think you could proably speed up your query execution by executing the stored queries in stead of running long "string queries" through the runsql method of the docmd object. Stored queries contains some optimization/query execute plan that usually speeds up execution vs running the string thingies.
For instance, I think this should work using ADO and 2002+ versions (don't think it'll work on 2000, though)
Code:
dim rs as adodb.recordset
dim cmd as adodb.command
dim prm as adodb.parameter
set cmd=new adodb command
with cmd
.activeconnection = currentproject.connection
.properties("Jet OLEDB:Stored Query")=True
.CommandText= "qryMyStoredQuery"
' resolve the parameters
for each prm in .parameters
prm.value=eval(prm.name)
next prm
.execute,,adexecutenorecords
end with
I don't do much DAO, but there's probably some equivalent methods there too.
Giving the Hourglass, also gives the impression something is going on:
docmd.hourglass true
' process
docmd.hourglass false
I use DoEvents, just doevents, after updating a label. DoEvents is a bit dangerous, cause, if you don't trap for it, the user may hit the same button again, starting the same process "while it is still processing"... I usually combine it with a form opened with the acdialog option.
To be sure you reference the correct form, you could pass the form to the routine you're performing this:
call runallmyqueries(me)
private sub runallmyqueries(frm as form)
' then a reference could look for instance like:
debug.print frm("nameofcontrol").value ' or
debug.print frm!nameofcontrol.value
DoCmd.RunSQL
or
CurrentDb.OpenRecordset
or
CurrentDb.OpenQuery
So long as you just call the query that's stored (not in VBA), it will be faster, but how much faster is relative. Furthermore, the purpose of this post was in reverence to letting the user know what's going on.. not how to speed up query build, execution, or run time. Though it is helpful, it can go in another thread (and I'm sure there are other ones on this forum that deal with database optimization).
To be more specific, there are posts that go into detail about how JET acts on a stored query, compared to VBA's on-the-fly queries.
Nevertheless, Roy is still right, but DAO is much easier to use.
Executing stored queries containing parameters using DAO without resolving the parameters? Are you sure? I thought you needed utilizing the parameters collection of a querydef object for that, not just openrecordset, runsql or openquery (does the runsql method of the docmd object work on stored queries? Openquery would probably work, but my quess is, it would be slow - here's a recent thread on the topic http://www.access-programmers.co.uk/forums/showthread.php?t=82887). Wouldn't something like this be a possible DAO version?
Code:
dim prm as dao.parameter
dim qd as dao.querydef
set qd=currentdb.querydefs("qryMyStoredQuery")
for each prm in qd.parameters
prm.value=eval(prm.name)
next prm
qd.execute
I post a reply where I see fit, thank you modest. I believe it is up to the original poster to evaluate what is relevant or not with regards to their question.
Please do not lecture me on forum etiquette, modest, I don't consider myself to be among those who need it most. Should you or someone else find my replies off topic, inflammatory... there are tools for that. PM a modarator or use the link in the upper right of a reply.
Rich: Originally I had I'm a big believer in using "Me" sparingly, but I thought it was written I'm a big believer in using "Me" (w/o the sparingly) so I changed it to I'm not big believer in using "Me" sparingly... but I caught the "sparingly" and now it's back to what I wanted =)
Using it sparingly does not mean "don't use it at all". I just think it's more descriptive and sometimes avoids errors if you don't use it. In certain cases it does no harm and helps you type your code faster.
Roy:
RoyVidar said:
Executing stored queries containing parameters using DAO without resolving the parameters? Are you sure? I thought you needed utilizing the parameters collection of a querydef object for that
My example was just common calls you could use on stored queries. This would be an example in DAO:
Code:
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset "queryName"
Me.Object.Value = rs![Field Name].Value
RoyVidar said:
I post a reply where I see fit, thank you modest. I believe it is up to the original poster to evaluate what is relevant or not with regards to their question...
I somewhat agree, however, my point was that the new question had to do with run time. It should have been posted as a new thread. There are other users who actually search the forums, you know? Instead of answering the question... you and I both should agree that we should have told him to start a new thread.
Please do not lecture me on forum etiquette, modest, I don't consider myself to be among those who need it most.
