Run an Access query from VBA in the background

[KriegerBob2]

Does anyone know of in VBA to have a query in the background. I already have warnings set to false. What I'd like to happen is the query is started then control returns to the subroutine while the query runs in the background.
So the user can move to another screen while the query runs.

[zmbd]

KB2;
You'll find that VBA is not normally an asynchronous programing language. That is to say, that each line of code normally executes and then the next, and so on.

With that said, What I have done with queries that seem to take awhile is to open a non-modal form that handles the query and once the form is open, the next line executes: So you get VBA-Line1-somecode, VBA-Line2-opensform, VBA-Line3-somemorecode- where line 1, 2, 3 execute in order. In the meantime, the form has opened and its onload and open events are running.
Now once that form is open, you can assign that query to be the record set for that open form and go from there.

In reality, I've only one database I need to do this for and that is because it isn't even remotely normalized!

[ADezii]

You should be able to Execute a Query Asynchronouosly by using an ADODB Command Object along with the adAsyncExecute Option of the Execute Method. Let's suppose that you had an enormous Products Table and that you wanted to increment the Reorder Levels for all Products by 25%. Let's further suppose that you did NOT want to wait until the Query was finished executing before you ran some other Code:

1. Set a Reference to the Microsoft ActiveX Data Objects X.X Object Library
2. Execute the following Code which should run the Query Asynchronously (Not Wait):
   

   Dim cmd As ADODB.Command
   Dim strSQL As String
   
   Set cmd = New ADODB.Command
   
   strSQL = "UPDATE Products SET Products.ReorderLevel = [ReorderLevel]*1.25;"
   
   With cmd
     .ActiveConnection = CurrentProject.Connection
     .CommandText = strSQL
     .CommandType = adCmdText
       .Execute adAsyncExecute
   End With
   
   'Execution will immediately fall to the Sub-Routine where Code execution
   'will continue while the Query is still running
   Call SomeRoutine

[zmbd]

ADezii
Very true; however, OP didn't provide details as to wither this was all-in-one access or FE/BE relationship. Which is something I should have asked... wherein you could send the backend off to do it's work and leave the frontend open to the user.

I also tend to avoid the active-x stuff as MS has a history of breaking their stuff with an errant kill bit :( or the object is blocked by IT somehow - don't ask... we still don't know how they did it - just that it required a new, clean installation of my desktop.

[ADezii]

@zmbd:

wither this was all-in-one access or FE/BE relationship

Would not make a difference unless it was a Client/Server architecture (*.adp), would it? The Query would still be processed at the FE, wouldn't it?

[zmbd]

Take for example a SQL-Server. The stored procedure when called would be asynchronous to the access front end.
Also if the query is sent as a pass-thru query wherein the JET/ACE doesn't deal with the SQL in the least - it simply passes the SQL to the backend.

For example. The main Lab LIMS (laboratory information management system) is ran on a SQL server. I have a front-end built (actually, one of the IT guys tweeked one of my frontends to get the ODBC calls correct) using MSACCESS to pull several of my reports. In this case, the calls are sent to the SQL stored procedure and the reports are que'd and sent back. I'm not 100% sure what they've done on SQL side to get that to work (I suspect SSRS is running); however, the PDF shows up in my email :). The server is HUGE and pulls from labs from around the world; thus the asynchronous query is a must - as called from the stored procedure ( How to Execute SQL Stored Procedures from Microsoft Access ) (also a tidbit here: Get stored procedure output value back in VBA.

So, as I mentioned... totaly remiss of me not to inquire about the nature of the database.

[NeoPa]

I believe ADezii's point is that a connection could even be made to the currently open FE database and his approach would still work Z.

For completeness, I should add that there is also a dbRunAsync flag available when opening DAO objects also, but which only works with ODBCDirect workspaces. DAO ODBCDirect Connection and QueryDef objects also support an Execute() method which takes a dbRunAsync flag.

[zmbd]

Masters,
I still am learning. :)
None-the-less I get the impression here of fe/be and not all-in-one.... but this then leads me to the following:

QueryDef objects also support an Execute() method which takes a dbRunAsync flag

Now is this within a DAO ODBCDirect Connection or is these native to both an all-in-one and a split db?

[NeoPa]

Z:
Now is this within a DAO ODBCDirect Connection or is these native to both an all-in-one and a split db?

I'm not quite sure I understand exactly what you're asking (or maybe even implying), but my understanding is that a DAO ODBCDirect connection could be used to connect to a BE file (Only ODBC though, not Jet.), although I'm not sure whether or not any BE can be connected using that type (I would guess not from the wording of the .Connect Help page). It seems to me that the splitting of any part of the project into FE/BE or not has no bearing whatsoever on whether this feature is available. Only the type of link to the file used for .Execute().

Does that make sense?

Z:
Masters,
I still am learning. :)

Like the rest of us Z, you're learning as you teach. I'm certainly not past learning from other experts (and even those not recognised as such) on here. I'm sure (absolutely sure that is) that were you not otherwise employed you could earn your living at this. No worries.