none
QueryDef Object Error When Accessing SQL Property RRS feed

  • Question

  • I have written a routine to enumerate the queries in Access database and save the properties to a table;

    Dim db          As DAO.Database
    Dim qdf         As DAO.QueryDef
    Dim prp         As DAO.Property
    dim rs          As ADODB.Recordset
    
    Set db = CurrentDb
    
    'Code to open recordset...
    
    For Each qdf In mDb.QueryDefs
        rs.AddNew
        rs.Fields("QryName").value = qdf.Name
        rs.Fields("SQL").value = qdf.SQL
        rs.Fields("CreatedDtTm").value = qdf.Properties("DateCreated").value
        rs.Fields("UpdatedDtTm").value = qdf.Properties("LastUpdated").value
        rs.Update
    Next qdf

    My problem is that if the SQL for the query is invalid, eg if a table referred to in the SQL has been deleted or the query contains an ambiguous outer join, then Access raises an error.  Attempting to access the property again returns a blank string.

    Is there any way to prevent Access parsing the SQL so the error isn't raised?

    Thanks in advance.

    Friday, May 11, 2012 1:54 AM

All replies

  • Your code as is doesn't compile. You also avoid mixing DAO and ADODB definitions, use one or the other exclusively. However ADODB has superseded DAO.

    I wrote the following that prints to the Immediate Window successfully so I suspect its your failure to open the recordset successfully that's the problem.

    Sub test()
    Dim db          As Database
    Dim qdf         As QueryDef
        Set db = CurrentDb
        For Each qdf In db.QueryDefs
            Debug.Print qdf.Name, qdf.SQL, qdf.Properties("DateCreated").Value, qdf.Properties("LastUpdated").Value
        Next qdf
    End Sub
    


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, May 11, 2012 3:01 AM
  • Hi Rod,

    The code I included covered the salient issue regarding the error I received - the code required to instantiate the recordset object wasn't included.  The code compiles with no problems and does enumerate the querydef objects successfully.  It is only the queries that can't run because of a problem with the SQL statement that cause a problem.

    I have also used DAO and ADODB objects together for years without any problem.  Why do you say you should use one or the other exclusively?

    Regards,

    Ray

    Friday, May 11, 2012 3:10 AM
  • Biggest cost in code is maintaining it. Mixing causes increased maintenance code and unless you are careful time consuming bugs occur due to incompatibility between two systems.

    Does my sample code work? If so then your recordset code is the problem.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Friday, May 11, 2012 9:05 PM
  • Hi Rod,

    Before replying can you please read my posts.  My first post specifically stated that the code to instantiate the recordset was left out.  My second post specifically states that the code compiles and runs successfully and successfully runs against the majority of queries in the database.

    The problem is that if the SQL statement for a query is invalid, ie it won't run from the Access IDE due to a temporary table being deleted, an ambiguous outer join, etc, then an error is generated on the line;

    rs.Fields("SQL").value = qdf.SQL

    If you then attempt to access "qdf.SQL" again it is Null, Access appears to "throw away" the invalid statement.

    Ray Brack

    Friday, May 11, 2012 11:47 PM
  • When asking for help, you are more likely to get help if you make it easy for us volunteers to find out exactly what your issue is and which line of code is causing the problem if you add a comment to say this.

    I still want to know if my code sample works with your invalid query.

    If it doesn't then I think you are out of luck.

    Help on the SQL property suggests the code has to be valid, but I think for execution only, so why the property cannot be read I don't know.


    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    Saturday, May 12, 2012 12:52 AM
  • Hi Rod,

    Just got back to work and tried your code but get the same error.  It seems strange to me that Access would attempt to validate the SQL in this situation but it seems there is no way arounf the problem - if the SQL is invalid you can't read the property?

    Ray Brack

    Sunday, May 13, 2012 10:31 PM
  • A possible workaround is to use error handling:

    For Each qdf In mDb.QueryDefs
        rs.AddNew
        rs.Fields("QryName").Value = qdf.Name
        On Error Resume Next
        rs.Fields("SQL").Value = qdf.SQL
        If Err Then
            rs.Fields("SQL").Value = "- Error in SQL -"
        End If
        On Error GoTo 0
        rs.Fields("CreatedDtTm").Value = qdf.Properties("DateCreated").Value
        rs.Fields("UpdatedDtTm").Value = qdf.Properties("LastUpdated").Value
        rs.Update
    Next qdf


    Regards, Hans Vogelaar

    Sunday, May 13, 2012 10:45 PM
  • Hi Hans,

    True - that would handle the error but SQL statement for the query would still not be stored.  Since the objective is to store the query statement so that it can be recreated later we are no closer to a solution.

    I was hoping that Access stored the SQL statement in the hidden MSysQueries table however it recreates the the query from this table by storing the tables, fields, join, grouping, ordering and where values in separate rows using a combination of attributes, flags and names.  From this information it is possible to recreate the query however I will need to do a bit of work to reverse engineer the query.

    Monday, May 14, 2012 2:04 AM