locked
Async Query RRS feed

  • Question

  • 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 my back-end database is MS SQL Server.

    Regards,
    K
    Tuesday, May 29, 2018 9:51 AM

Answers

  • Not sure why the “additional” queries don’t’ work. However, once the code has run one time, then you can open up query2 etc. in design mode, look at the sql generated, and in fact can simply run the query to see the results. You can also paste in the results to SSMS studio to ensure the SQL is ok, but really, just clicking on to run query2 or 3 etc. should return results – if they don’t, then look at the “updated” sql you saved in each query – that should shed light on what is going wrong.

    As for the connection? Sure, you can set it each time but I suspect that you also have linked tables. It is “in general” that your table re-link routines will now also include any PT query in the re-link code (so you loop tabledefs and also now querydefs to update the connection). I am guessing, but you likely have some table re-link code to point linked tables to the correct server, and you can point the PT queries the same way. It should not matter (but might) to set the connection each time – I would try and avoid this if possible (the SQL server cache of the connections may well be disturbed by setting this each time and you likely don’t need nor want to do this).

    I would after running the code once, then exit the form. Now simply open + look at query2 etc. So you are free to play with + look at the query 2, 3, 4 and so on to see what was saved.

    In fact, just reading what I just wrote, the PT query approach is quite nice, since the SQL used for all those PT query are saved each time. You thus can look at them, and try them after the code been run.

    As noted you can cut + paste the sql from “any” of those several PT queries into SSMS and try them that way. This would help you see what results they are (or are not) returning.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by KhurramKZ Wednesday, June 6, 2018 6:51 AM
    Thursday, May 31, 2018 3:01 PM
  • Thanks. I picked up why I was not getting PT queries up to work. My own mistake, not to mention due embarrassment.

    Thanks for the help, i've converted all to PT Queries and link them during the time tables are linked to each server.

    My issue is solved now, with a big contribution received from another forum too.
    Sorry I know it is cross-questioning but it was such a big issue for me that I though to get help from couple of boards.

    https://access-programmers.co.uk/forums/showthread.php?t=299940

    Thanks for support.

    BR,
    K

    • Marked as answer by KhurramKZ Wednesday, June 6, 2018 6:50 AM
    Wednesday, June 6, 2018 6:50 AM

All replies

  • No, Access doesn't support asynchronous processing.

    Why not only twittery the other combo boxes upon focus?  That would eliminate retiring for no reason unless the user specifically goes to use that combo box.

     

    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, May 29, 2018 10:00 AM
  • Not much practical. And if you could start executing some of the quires async, then some combo box etc. might finish before others – and you not be able to manage this mess.

    What I would consider is creating PT queries for all of the combo boxes. You then update the queries (this will occur VERY fast, and not update the combo boxes). At that point, you can execute a requery on each combo. This should occur VERY fast.

    In other words, the update to the 15 PT quires will NOT cause a data pull until such time you tell the controls to re-query.

    If the columns in question have indexing, then such a process should not take that long.

    However, an update on 15 controls will take some time – regardless of the size of the data being pulled.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Tuesday, May 29, 2018 6:15 PM
  • Technically Microsoft Access only supports single-threading; however, if you use Classic ADO with an external database such as SQL Server there is support for asynchronous queries. I haven't tried it before in Access but you may want to give it a shot to see if it's useful.

    https://stackoverflow.com/questions/16167478/executecomplete-adodb-connection-event-not-fired-with-adasyncexecute-parameter


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, May 29, 2018 9:19 PM
  • Good idea. Will try that if I could not find Async queries helpful, or if i could not deploy them :)

    Wednesday, May 30, 2018 9:54 AM
  • 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 even in case of Action queries. 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

    Thanks,
    K

    Wednesday, May 30, 2018 9:56 AM
  • First of all, you can’t really reduce “async” time, since async solutions ASSUMES you don’t really care much about the order of things when they finish.

    If you have say 5 combo boxes, and each one is a “cascade” or dependant on the previous one then you CAN NOT use an async solution. Because you will not know which of the 5 SQL queries will be done first and you can’t control order of which one is finished. So such cascade code will HAVE to wait for the previous query to finish and thus you don’t gain anything here!

    So at the risk of sounding VERY rude or becoming condescending here? I will just say please re-read my other post here – I state that this will not work due to the combo boxes have dependencies on the previous ones. If the combo boxes were NOT depend on each other, then no problem.

    >>>>>>

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

    The built Query def objects are DAO and they don’t support async operations.  And you don’t need all that above extra code (you have too many things), see my example below.

    If you really need async operations, then you have to adopt ADO record sets, and you likely have to add a timer event to the form, since you NOW have to check + know when the queries are done and THEN have the dependant combo boxes filled based on WHEN the query has finished (you wind up waiting anyway!!!)

    Anyway, I do however suggest using PT queries, and I'll share some tips to really speed this up.

    First up:

    To modify the PT query for a combo box, say this one to a selected Country in the previous combo box, you code can be this:

       With CurrentDb.QueryDefs("qryPassCity")

          .SQL = "select id, City from tblCity where Country = '" & Me.cboCountry & "'"

       End With

       Me.cboCity.Requery

    So because you have lots of controls, then note above how I did not even create a query def var, did not set returns records = true etc etc etc. You don’t need to set all that in code – set it in the query property sheet. I did not even create a querydef var.

    Now if you just had one combo – hey lots of extra code don’t matter – but you have “lots” of controls here, so reduced code since we doing this “many” times is a rather important goal here.

    The other tip for speed is set your combo box to ONLY HAVE the name of the PT query as the source of the combo box.

    DO NOT LET or LAUNCH the query builder for the combo box. We are looking to get max speed here – and placing JUST the query name in the combo box will improve performance by a good margin. This time saving trick is not huge with 1 or 2 combo boxes, but after 3-5 on a form, the time REALLY starts to add up and will be notable.

    So if you only had one combo box on the form – no big deal that you get a .3 second delay. However, if you have 10 combo’s, that that .3 becomes 3 or even more seconds.

    Now because we NOT using the query builder Access side and NOT stuffing SQL right into the combo box control?

    Well, that means is we have to set order in that Pass though query

    Eg:

       With CurrentDb.QueryDefs("qryPassCity")

          .SQL = "select id, City from tblCity where Country = '" & Me.cboCountry & "'" _

                 " order by City"

       End With

       Me.cboCity.Requery

    Of course if most combo boxes are “id, Text part”, then above would be a country code and thus a “id” and thus look like:

       With CurrentDb.QueryDefs("qryPassCity")

          .SQL = "select id, City from tblCity where Country = " & Me.cboCountry &  _

                 " order by City"

       End With

       Me.cboCity.Requery

    So I would try converting all the combo to PT quires and try the above. Do remember the trick to remove the SQL from each combo box and JUST use the name of the PT query for each. You still have to “match up” the number of columns in each PT query as to the number of columns you had for each combo box. While most are 2 columns (id, text part), you might have only one or some different setup – your PT queries will have to return the same number of columns, and since we using that “trick” to speed up the  combo boxes, then the order of columns from the PT query is NOW very important (it must match what you had).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Wednesday, May 30, 2018 9:46 PM
  • Thank you Albert for such a detailed answer. Reason I was convinced to do Async queries because we've an Android version of the program which was turning slow as well and there developers applied Async queries along with other changes to speed things up and this seems to work great on Android at the moment.

    Anyways, I've applied 15 PT queries and is like this:
        With CurrentDb.QueryDefs("qryPass1")
            .Connect = TempVars("ConnectionString").Value
            .Sql = "SELECT DISTINCT tblBoQ.TagNo " & SqlWhere & ParaWhere & " AND tblBoQ.TagNo IS NOT NULL"
        End With

        Me.CbxTag.RowSource = "qryPass1"

    Unfortunately, I've to use .Connect parameter in query because program is designed in a way that it could connect to any server just by changing the name of server in a table. So it has to pick up the name dynamically or once the server is changed I've to changed the connected string for 15 queries... every single time, which is not gonna work for me.

    After applying PT queries I've not seen significant increase in query running time via SQL Profiler, i've SQL Server 2014. All queries are running within 10-17 ms each, with/without PT queries, but I'll leave PT queries for now because i agree that they're faster. maybe this will work better as I change other components of From and Where in the query, and which I'm going to look in new few days.

    Now the issue is that only first query/parameter is returning result, other queries does return anything. As a result nothing is coming in other combox except the first one. Once I change back to normal Access queries, they again start to work normal. I couldn't figure out what's the issue??!

    I've made a screenshot and uploaded in google drive for you to have a look. If the parameter for a record source is not returned, the combo remains disabled.

    To be precise, this form opens and carry forward some parameters, which it uses in Where clause, from its parent form. I'm pasting full code for parameter selection form in Notepad++ text format and uploading in same google drive folder where I'm uploading the above screenshot discussed. Maybe it helps you to understand better?

    https://drive.google.com/open?id=15MTzVgUeVPqQQQ9RXRUNWUyiX2qVXUYc

    Thanks for help.

    BR,
    K

    Thursday, May 31, 2018 9:52 AM
  • Not sure why the “additional” queries don’t’ work. However, once the code has run one time, then you can open up query2 etc. in design mode, look at the sql generated, and in fact can simply run the query to see the results. You can also paste in the results to SSMS studio to ensure the SQL is ok, but really, just clicking on to run query2 or 3 etc. should return results – if they don’t, then look at the “updated” sql you saved in each query – that should shed light on what is going wrong.

    As for the connection? Sure, you can set it each time but I suspect that you also have linked tables. It is “in general” that your table re-link routines will now also include any PT query in the re-link code (so you loop tabledefs and also now querydefs to update the connection). I am guessing, but you likely have some table re-link code to point linked tables to the correct server, and you can point the PT queries the same way. It should not matter (but might) to set the connection each time – I would try and avoid this if possible (the SQL server cache of the connections may well be disturbed by setting this each time and you likely don’t need nor want to do this).

    I would after running the code once, then exit the form. Now simply open + look at query2 etc. So you are free to play with + look at the query 2, 3, 4 and so on to see what was saved.

    In fact, just reading what I just wrote, the PT query approach is quite nice, since the SQL used for all those PT query are saved each time. You thus can look at them, and try them after the code been run.

    As noted you can cut + paste the sql from “any” of those several PT queries into SSMS and try them that way. This would help you see what results they are (or are not) returning.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    • Marked as answer by KhurramKZ Wednesday, June 6, 2018 6:51 AM
    Thursday, May 31, 2018 3:01 PM
  • Thanks Albert for the tip, I'll change the connect thing the same way i setup tables.

    Other than that, I've checked and queries are executing correct and returning results when I see them in query window.

    What I've noticed is, that RowSource is not updating for Combo's except for 1st Combo and I could not find a single clue why it is not happening.

    I've double-checked query names, row-sources I've assigned to and any other thing i could think of which could go wrong, but all seems correct.
    Plus, on googling, i found out that I've to 'requery' all other combo boxes once any of the other combo is changed but this is not helping as well.

    I could just scratch my head at the moment without any outcome!! What am I missing please?

    Sunday, June 3, 2018 7:11 AM
  • Hi KhurramKZ,

    >>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.

    What is the relationship between these 15 comboxs? Why you need to rerun all them while one of them changed.

    I am wondering whether the data source for second combox will depend on value from the first one? Or all of them are independent.

    >>that RowSource is not updating for Combo's except for 1st Combo and I could not find a single clue why it is not happening

    Could you try define one method which will query all of 15 queries and bind the data source to combobx, and call it from combobox change event?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, June 5, 2018 6:25 AM
  • Thanks. I picked up why I was not getting PT queries up to work. My own mistake, not to mention due embarrassment.

    Thanks for the help, i've converted all to PT Queries and link them during the time tables are linked to each server.

    My issue is solved now, with a big contribution received from another forum too.
    Sorry I know it is cross-questioning but it was such a big issue for me that I though to get help from couple of boards.

    https://access-programmers.co.uk/forums/showthread.php?t=299940

    Thanks for support.

    BR,
    K

    • Marked as answer by KhurramKZ Wednesday, June 6, 2018 6:50 AM
    Wednesday, June 6, 2018 6:50 AM