Async Query (1 Viewer)

[khurram7x]

Hi,


Is it possible to run query Asynchronous in Access, like in C# or Java??


I've a parameter selection form of 15 fields represented by 15 combo boxes. If one parameter is selected from any of the combo's, then all the other combos should have to re-run the query to show only narrowed down version of remaining options based on selected option in last combo, against the RowSource of remaining combos.
It means, once I selected an option from any combo, all the 15 queries need to re-run ONE-BY-ONE to update other combo's row sources... and this takes time on larger databases.


If query could be made Async in Access, it'll help me big time. Or if there's any better solution please?
Please note that back-end of my database is MS SQL Server.


Regards,
K

 

[CJ_London]

I think you are talking about what is called cascading combo's.

it is pretty straightforward, just basing the rowsource with a criteria linked to the previous combo

combo1 rowsource

SELECT ID, Name FROM myTable1

combo2 rowsource

SELECT ID, Name FROM myTable2 WHERE FK=[Combo1]

combo3 rowsource

SELECT ID, Name FROM myTable2 WHERE FK=[Combo2]


then in the after update event of each combo put

me.requery

if it is running slow, that may be down to your sql server or network being slow. Or perhaps your tables are not indexed

 

[NauticalGent]

PMFJI,In addition to CJ's answer, here is a link that I have found extremely useful when dealing with 3 or more ComboBoxes. It even provides the code in case you are feeling lazy, which is about status quo for me...http://www.fmsinc.com/microsoftaccess/forms/combo-boxes/cascading.html

 

[MajP]

The openrecordset method would allow you to do this on an ODBC or querydef. Then you could bind the recordset to the combobox.

The Execute, MoveLast, OpenConnection, and OpenRecordset methods feature the dbRunAsync option. This allows your client application to do other tasks (such as loading forms, for example) while the method is executing. You can check the StillExecuting property to see whether the task is complete, and terminate an asynchronous task with the Cancel method

 

[The_Doc_Man]

One comment only... you say that your backend is SQL Server. Good, an Async query can run in that context, as MajP has stated. So in this case you are OK and should get some success out of this issue.

For clarification, this ONLY works when the back end is an active database server such as the SQL Server, ORACLE, or some other SQL-based engine. If you were doing this with a "pure" Access back-end, the answer is very different. Others reading this should take note that a pure Access back end does not support asynchronous queries.

 

[khurram7x]

Thanks guys. For clarification, it is not cascading combos. In my case, you're allowed to select any of the 15 options right from the start. Once you select first option, other 14 options should re-query and show only options available based on first selection. There's no sequence as in cascading combo.

I understand that I've a SQL Server backend so i could use it with PT queries, but not getting clear sample over the internet how to execute it with SELECT QueryDef. Since in my combo case, they're all Select queries...

And once dbRunAsync is used, how to verify that it is working. Should I write a VBA function which will contain all the 15 queries and then assign results to RowSource?

Additionally code runs line by line, so while system will be executing first query, will the system go to the next query without waiting for first query to finish?
This is bit different than other languages, C# for instance, where all the ASync queries are first collected and then run a marker which triggers all queries marked as ASync together IMHO.

Here's a code for one of the SELECT Query, I'm not clear which part of the query should I use dbRunAsync.

Function RunPassThroughSELECT(strSQL As String)

Dim qdfPassThrough As QueryDef, MyDatabase As Database
Set MyDatabase = CurrentDb()
Set qdfPassThrough = MyDatabase.CreateQueryDef("qrySQLPass3")

With qdfPassThrough
.Connect = TempVars("ConnectionString").Value
.Sql = strSQL
.ReturnsRecords = True '**Should be false for action queries or non-visible queries
.Close
End With

Application.RefreshDatabaseWindow
End Function

Another code below for ACTION query, where I understand I should mentiond dbRunAsync with Execute commend. Correct me if I'm wrong please:

Function RunPassThroughACTION(strSQL As String)

Dim qdfPassThrough As QueryDef, MyDatabase As Database
Dim strConnect As String

Set MyDatabase = CurrentDb()
Set qdfPassThrough = MyDatabase.CreateQueryDef("qrySQLPassA")

With qdfPassThrough
'Debug.Print .Name
.Connect = TempVars("ConnectionString").Value
.Sql = strSQL
.ReturnsRecords = False '**Should be false for action and non-visible queries
.Execute '**Executes the ACTION QUERY
.Close
End With

Application.RefreshDatabaseWindow
End Function

Thank you in advance.

 

[MajP]

https://doc.helplib.com/help/294682d28adcdaa61c0d4e067df66821

This has some good examples. Look at the second for running a querydef. I believe you can do ADODB as well as DAO.

 

[sonic8]

https://doc.helplib.com/help/294682d28adcdaa61c0d4e067df66821

This has some good examples. Look at the second for running a querydef. I believe you can do ADODB as well as DAO.

MajP, may I suggest you take a look at those samples yourself. They are all about ODBC-Direct-Workspaces, which have been removed with Access 2007.


To my knowledge it is not possible to execute DAO-Queries asynchronously (in current versions of DAO).


I dare say, ADO is the only option to do this. - But I would love to be corrected.

 

[MajP]

Does look like ADO is the more likely and flexible possibility https://www.labath.org/docs/sys/mssql2000/mdacxml/htm/wpmdac_tf_asynchronously_executing.htm
Looks like you can then trap the executecomplete and then bind the recordset to your combobox.

 

[MajP]

@Sonic8
If there is an existing pass through queryDef (or you build it), could you just run that asynch?
Set rst = qdf.OpenRecordset(Type,dbRunAsync,LockType)
I do not have any way to test, but do not understand why it would not work.

 

[sonic8]

@Sonic8
If there is an existing pass through queryDef (or you build it), could you just run that asynch?
Set rst = qdf.OpenRecordset(Type,dbRunAsync,LockType)
I do not have any way to test, but do not understand why it would not work.

No, I'm afraid it will not work.



If you look up the documentation of QueryDef.Execute, or any method where dbRunAsync can be (theoretically!) used , you'll find the following statement:

dbRunAsync - Executes the query asynchronously (ODBCDirect Connection and QueryDef objects only).

For clarification: I understand this to mean ODBCDirect Connection and
ODBCDirect QueryDef.

 

[MajP]

and ODBCDirect QueryDef

Thanks did not make that association.

 

[khurram7x]

How do I convert my above Query to ADO and use it as Async?
As a reminder, I've a SQL Server backend.

 

[MajP]

Did you try doing like the example in #9? That example is using a sql server backend.

 

[The_Doc_Man]

Re-reading this, I have a comment about the overall process. If you are filling in the .RowSource of a combobox with an updated query followed by a .Requery, you are asking Access to do the synchronization - and Access is coded ONLY for synchronous operations on those background queries. I didn't think about that before, but I suspect that you cannot set a combobox .RowSource query to run asynchronously.

 

[Minty]

I also question how this can be taking so long.
As each combo is somehow based on another one (and I have my doubts as to how this can be?) there must be a solid relationship between those queries/record sources...

Can you show us some sample data and the form to see if this is being done in a sensible manner?

 

[MajP]

Re-reading this, I have a comment about the overall process. If you are filling in the .RowSource of a combobox with an updated query followed by a .Requery, you are asking Access to do the synchronization - and Access is coded ONLY for synchronous operations on those background queries. I didn't think about that before, but I suspect that you cannot set a combobox .RowSource query to run asynchronously.

What? You are late to the game. That is not what the OP wants to do, and does not make much sense. He wants to run an asynchronous query on the sql server and then bind the returned recordset to the combobox. There is no rowsource in play.

 

[MajP]

I would test this first with a single combo just to ensure you can do this asynch. With multiple combos I have and idea how I would do it, but I am no Sql Server expert. I would think you would need to trap the ExecutionComplete event before doing the next.
I am going to assume you do not want to rewrite the pass through queries and would be happy taking the sql string from them. I would try something like this for a single asynch query and if this works then try the next. As I said this is untested, but trying to grab the sql string from the pass through then send it to the procedure to open asynch, then when it is done processing "bind" it to the combobox. If you can do one then you should be able to do them all in series. I do not think you can do them all in parallel. If you open multiple connection I still think they get pooled.

Public Sub LoadFromPassThrough(PassThroughName as string)
  dim strSql as string
  strSql = currentdb.queryDef(PassThroughName).sql
  GetRecordset(strSql)
end Sub 

sub GetRecordset(strSql as string)
   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command
   Set conn = New ADODB.Connection

   conn.ConnectionString = _
   "Provider=SQLOLEDB;Data Source=sql70server;" _
      & "User ID=sa;Password='';Initial Catalog=pubs"
   conn.Open

   Set cmd.ActiveConnection = conn
   cmd.Execute strSql, , adAsyncExecute
   Debug.Print "Command Execution Started."

End Sub

Private Sub conn_ExecuteComplete(ByVal RecordsAffected As Long, ByVal _
   pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal _
   pCommand As ADODB.Command, ByVal pRecordset As ADODB.Recordset, _
   ByVal pConnection As ADODB.Connection)
   
   set ComboX.recordset = pRecordset
End Sub

You will have to modify the connection string and other connection properties.

 

[The_Doc_Man]

MajP - re-reading the OP's posts, I did not see where he was planning to do a late bind of the combobox. If I missed that point, OK. I'm human and it happens. But the only place I clearly saw that idea was in your posts, not his - which is why I said what I did. If I missed his comment, all I can say is Whoops - but I was answering what I thought had been asked by the OP.

My concern is that when you force a reset of the form's .Recordsource by directly assigning a new .Recordset or by defining a new SQL source, MSDN says you automatically requery the form, no choice in doing so. I've looked on several tech sources for the corresponding behavior for .RowSources, but those articles are harder to find. If .Rowsources behave like .Recordsources, though, you will get an automatic requery, which would sort of defeat the whole ASYNC operation, I think.

The problem is that if you look up MSDN's version for the combobox.Recordset, it exists but they give a description that seems to be more like a form.Recordset. I don't know if that means they think the two behave the same, but let's just say it would not surprise me to find that changing the combobox.Recordset by assignment causes a .Requery implicitly.

 

[sonic8]

My concern is that when you force a reset of the form's .Recordsource by directly assigning a new .Recordset or by defining a new SQL source, MSDN says you automatically requery the form, no choice in doing so.

Have you got a link to this documentation? It sounds pretty wrong to me and I wasn't able to find anything in line with your statement regarding the .Recordset.


If you change the Recordsource or Rowsource this will automatically trigger a Requery, obviously. However, if you change the Recordset this will not trigger a requery. You just supplied the required data already within the Recordset, so a requery would be completely pointless. - This applies to forms and list-/comboboxes all the same.