Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    15

    Unanswered: DAO and QueryTables

    Hello,

    I am building a revenue projection system in Excel VBA that is powered by Access DAO. This is my first Access project in VBA.

    It currently takes all of the sales information, runs it through a bunch of query tables, which the code creates, and then walks through the various query tables and creates the final revenue table.

    I use the following sort of logic to test for tables existence, to delete if it exists and rebuild, or to simply create if it does not exist:

    Code:
        test = db.TableDefs(qryNm).Name
    
        If Err Then
           strSQL = ""
            qd.Sql = strSQL
        Else
            DBEngine(0)(0).QueryDefs.Delete qryNm
            strSQL = ""
            qd.Sql = strSQL
        End If
    This was the only querytable test that I was able to find after some searching, which relies on an 'on error resume next' to execute it. The problem is that it does not seem to reliably accomplish what I need. Sometimes tables are not deleted, even though they are there or sometimes the tables randomly claim there are circular references, yet running the code again, or a few more times, will fix it (The SQL itself works fine).

    The idea behind this entire project is that the user will not have to interact with Access at all, all of the end results will flow to Excel, but I am having serious concerns about the methodology in which I am going about this.

    Anyone have any comments or help?


    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You must first decide whether you're dealing with Tables or with Queries. There is no such thing a a "table query". Transposed in the DAO model, a Table is accessed through a TableDef object, while a Query is accessed through a QueryDef object. Both are not equivalent.

    For testing the existence of a table in a database, you can use:
    Code:
    Function TableExists(ByVal TableName As String) As Boolean
    '
    ' Note: db is the Database object.
    '
        Dim tdf As DAO.TableDef
        
        For Each tdf In db.TableDefs
            If tdf.Name = TableName Then
                TableExists = True
                Exit For
            End If
        Next tdf
        Set tdf = Nothing
        
    End Function
    And for a Query:
    Code:
    Function QueryExists(ByVal QueryName As String) As Boolean
    '
    ' Note: db is the Database object.
    '
        Dim qdf As DAO.QueryDef
        
        For Each qdf In db.QueryDefs
            If qdf.Name = QueryName Then
                QueryExists = True
                Exit For
            End If
        Next tdf
        Set qdf = Nothing
        
    End Function
    For a Table, you can then use:
    Code:
    '
    ' Note: db is the Database object.
    '
    If TableExists(TableName) Then Db.TableDefs.Delete TableName
    And for a Query:
    Code:
    '
    ' Note: db is the Database object.
    '
    If QueryExists(QueryName) Then Db.QueryDefs.Delete QueryName
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    15
    I am using both. The text file is written to a table. That table is then queried off of. Finally, those queries are crawled and updated into another table.

    My apologies if the terminology is wrong. I was under the impression that there were temporary queries that differed from the standard Access permanent queries, which was what I was trying to distinguish.

    The code that you pasted should be very helpful, thank you.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    From MSDN (http://msdn.microsoft.com/en-us/libr...ffice.12).aspx)
    ...
    You can also create temporary QueryDef objects. Unlike permanent QueryDef objects, temporary QueryDef objects are not saved to disk or appended to the QueryDefs collection.
    ...
    To create a new QueryDef object, use the CreateQueryDef method. In a Microsoft Access workspace, if you supply a string for the name argument or if you explicitly set the Name property of the new QueryDef object to a non–zero-length string, you will create a permanent QueryDef that will automatically be appended to the QueryDefs collection and saved to disk. Supplying a zero-length string as the name argument or explicitly setting the Name property to a zero-length string will result in a temporary QueryDef object.
    ...
    Last edited by Sinndho; 01-27-12 at 13:35. Reason: Supplemental infos.
    Have a nice day!

Posting Permissions

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