DAO and QueryTables

ERahn1 · #1 (**permalink**) 01-27-12, 10:40

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.

Sinndho · #2 (**permalink**) 01-27-12, 12:17

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

ERahn1 · #3 (**permalink**) 01-27-12, 12:25

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.

Sinndho · #4 (**permalink**) 01-27-12, 12:28

You're welcome!

From MSDN (http://msdn.microsoft.com/en-us/libr...ffice.12).aspx)

Quote:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech