How to find out from VBA which table a query in MS ACCESS creates?

Question

I wrote the below Sub to run a few Make-table queries, of which some use a parameter Some_date

Sub run_query(queryName As String, Optional Some_date As Date)
    Form_Select_input.logProgress queryName

    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs(queryName)
    On Error Resume Next
    qdf!Date_after = Date_after
    On Error GoTo 0
    qdf.Execute
    Set qdf = Nothing
End Sub

A typical query looks like

PARAMETERS Some_date DateTime;
SELECT  Some_field
    ,   Other_field
INTO Some_Target
FROM Some_Source
    LEFT JOIN Other_Source
    ON Some_Source.key = Other_Source.key
where Some_Source.Transaction_Date > [Some_date];

Now this works the first time I run the queries, but the second time, gives an error the tables already exist, so I would like to write something like

Sub run_query(queryName As String, Optional Some_date As Date)
    Form_Select_input.logProgress queryName

    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs(queryName)

    On Error Resume Next
    DoCmd.DeleteObject acTable, qdf.Destination ' At first execution, the destination does not exist, but we resume next 
    qdf!Date_after = Date_after                 ' For some queries, the parameter does not exist, but we resume next
    On Error GoTo 0
    qdf.Execute
    Set qdf = Nothing
End Sub

Please help me replacing qdf.Destination with something that exists.

An idea would be to name that query similar to the table: `MyTable_Create` and `MyTable`. So if you want to execute the query `MyTable_Create` you know to search for a table named `MyTable` before, by cutting `_Create` from the query name. — AHeyne, Mar 13 '20 at 16:09

score 1 · Answer 1 · answered Mar 13 '20 at 17:43

The Access system tables can give you the name of the table a saved make-table query creates.

Consider this query:

queryName = "typical_query"
? CurrentDb.QueryDefs(queryName).SQL
SELECT Year(i.OrderDate) AS [Year], Count(i.OrderID) AS TotalOrders
INTO Some_Target
FROM Invoice AS i
GROUP BY Year(i.OrderDate);

First find the query's Id value in the MSysObjects table:

queryId = DLookup("Id", "MSysObjects", "[Name]='" & queryName & "'")
? queryId
-2147483409

Then find the MSysQueries row for that Id (ObjectId in MSysQueries) where the Attribute field value is 1. The name of the created table is stored in the Name1 field:

? DLookup("Name1", "MSysQueries", "ObjectId=" & queryId & " AND [Attribute]=1")
Some_Target

Be cautious with the system tables. You should be safe to only read their data. But avoid modifying the data, or else you may break your database.

This approach requires that your Access user has read permission for those tables. If your database does not allow them read permission, you can execute a GRANT SELECT statement to give it to them. (GRANT SELECT example)

score 1 · Answer 2 · answered Mar 15 '20 at 16:07

To get the target table, you can use this in your code:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs(queryName)
dim strTarget as string

strTarget = trim(Split(Split(qdf.SQL, "INTO")(1), " ")(1))
On Error Resume Next
DoCmd.DeleteObject acTable, strTarget

score 0 · Answer 3 · answered Mar 13 '20 at 14:59

0

you could just query the system tables ot see if the object exists.

If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 0 Then
    'do stuff
End If

answered Mar 13 '20 at 14:59

Doug Coats

6,255
9
27
49

The question is, what `tblName` should be – Dirk Horsten Mar 13 '20 at 15:44
Well if you are automating make tables queries you should have that lol – Doug Coats Mar 13 '20 at 17:52
If I were automatically creating `make table` queries, I would, indeed. That is not what I am doing. – Dirk Horsten Mar 13 '20 at 19:02
So you have the parameter that gives you the date part of the name....I am confused as to how you dont have that information? Am I just missing the point here? – Doug Coats Mar 13 '20 at 21:43

How to find out from VBA which table a query in MS ACCESS creates?

3 Answers3