Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: Determine If Query Ran Without Error

    Hey,
    I'm looking at somebody elses' code and need to figure out a way to determine if the queries that are run to be re-run.
    So for example:

    DoCmd.OpenQuery "queryX", acViewNormal
    'DoCmd.OpenQuery "queryY", acViewNormal
    DoCmd.OpenQuery "queryZ", acViewNormal

    Now, if one of those queries fails, I need it to go to retry that query. This is necessary cause sometimes data may change in the middle of a query for our application.

    How can I check for an error on a query?

    btw: these are mostly update queries which means i could use some kind of query to check if changes occured right after the update, but under some conditions that might not work either

    another possible solution would be to keep track of the current query running and if an error occurs, simply rerun that query.

    But what I'm more interested in is an unseccessful query notification instead of just knowing the error number.

    thx ahead of time.
    Last edited by tkat11; 03-23-04 at 16:33.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346

    Re: Determine If Query Ran Without Error

    If you are using ADO (or want to use it) you can declare your recordset and connection objects with events as so:

    Private WithEvents rs As ADODB.Recordset
    Private WithEvents cn As ADODB.Connection

    This way you can capture events or non-running queries with events like:

    RecordsetChangeComplete
    RecordChangeComplete

    or

    ExecuteComplete
    CommitTransComplete etc...
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    thx for the reply SCIROCCO.

    I've read up on RecordsetChangeComplete and am trying to follow an online example that I found. If I have difficulty, I'll post again and see if you can help me out yet again.

    Thx

  4. #4
    Join Date
    Oct 2003
    Posts
    73
    Now this question is really going to show my inexperience with Access VBA.

    The RecordsetChangeComplete event is only fired on a certain recordset object correct? Well if so, then how would I get a query that is run by the "DoCmd.OpenQuery "queryZ", acViewNormal" command to use events? queryZ is just a query that I created in the query design therefore it cannot have any events associated with it? (I'm probably wrong, but I don't know)

    Thx again

  5. #5
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    Yes the RecordsetChangeComplete event is fired with an ADO recordset object. To have it fire with your query you will have to call your query in code using the recordset object. Here is an example:

    'Declared Globally
    Dim WithEvents cn as ADODB.Connection
    Dim WithEvents rs as ADODB.Recordset

    -----------------------------------------------------
    Set cn = new ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\Test.mdb;Persist Security Info=False"

    cn.open

    set rs = new ADODB.recordset

    rs.actiiveconnection = cn

    rs.open "SELECT * FROM Query"

    'Now you put in code for the events

    ----------------------------------------------------
    'When finished
    rs.close
    cn.close
    set rs=nothing
    set cn = nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

  6. #6
    Join Date
    Oct 2003
    Posts
    73
    thx for the reply again.
    it's too bad you can't just call the query like its already called (just calling the predefined query with the docmd command ie. "DoCmd.OpenQuery 'queryZ', acViewNormal").
    so i'll keep on trucken here.

    thx again

  7. #7
    Join Date
    Oct 2003
    Posts
    73
    well, I figured out a simple solution. What was happening supposedly is that these queries were running but during the run, some of the tables being accessed in the queries were being written to thereby causing a query run-time error. So, all i did was add an error handler to the code and done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •