How to get fields of a make-table query in VBA?

[jasmith4]

I'm writing VBA code to open objects and document them. When it gets to a make-table query I'd like to get the fields of the table it makes along with each one's source. But when I set a QueryDef object to the make-table query, then set a Fields object to QueryDefObject.Fields, the collection is empty, i.e., its Count property is 0. I can't even get the name of the table it makes or the location (could be another database), and I can't guarantee that the table exists already.

I want to write up a log saying:

Make-table query "MyQuery" makes table "MyTargetTable" in "MyTargetDatabase"
MyTargetTable will have the following structure:
1. TargetField1/FieldType1/FieldSize1: source is SourceTable1/QueryField1
2. TargetField2/FieldType2/FieldSize2: source is SourceTable2/QueryField2
etc.
...even if MyTargetTable has never existed. Isn't this possible?

 

[Micron]

You should always post what you've tried in a situation like this, lest I propose the same thing. This works for me

Public Sub getQryFields()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As Field

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
For Each fld In qdf.Fields
Debug.Print fld.Name
Next
End Sub

Edit: this is not a make table query - not sure if you're saying it works for select but not other types. Might need more info from you if that doesn't work.

 

[Micron]

I discovered that a make table query doesn't seem to have a fields collection. My idea then was to create a new query def object from all of the select portion, then see if I could enumerate its fields. This worked:

Public Sub getQryFields() 'you'd modify the code to accept the table name as a parameter
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As Field
Dim sql As String
Dim pos As Long

Set db = CurrentDb
sql = db.QueryDefs("MkTbl1").sql 
pos = InStr(sql, "INTO") 'find the INTO part
sql = Left(sql, pos - 2) 'eliminate the I and space before it
Set qdf = db.CreateQueryDef("", sql) 'not assigning a name prevents saving the query def in the db
For Each fld In qdf.Fields
Debug.Print fld.Name
Next
Set db=Nothing
Set qdf = Nothing
End Sub

 

[jasmith4]

Correct -- if Query1 were a make-table query, your code would print nothing.

 

[jasmith4]

Excellent idea -- I'm going to try that! But it's not quite complete: the syntax of a make-table query's SQL is:

- SELECT expressions INTO table FROM tables-or-queries [GROUP BY expressions] [WHERE conditions] [HAVING conditions] [ORDER BY expressions]

So you have to remove only "INTO table" by finding the FROM after it. In my case I also want the target table's name, which is between INTO and FROM.

 

[jasmith4]

I suppose I could/should apply similar thinking to append queries, whose syntax is:

- Append: INSERT INTO table (fields) SELECT...

Now, what about update queries, which can update more than one table???

- Update: UPDATE tables/queries SET field=expr, field=expr...

 

[Micron]

You did say you wanted the source, but I missed/forgot that. I don't know if other action queries have field collections or not. Once I create a query that works, I never worry about what comes from where until I need to use it elsewhere. If I think it needs documentation, I add comments to the query properties and have never desired to have documentation at the level you're after, especially in an environment where the boss always seemed to come back and ask for additional data after telling me "That's all I need".

Your situation makes me think this is another reason why using a consistent naming convention (even if it's your own) is so important. All my table names start with tbl. I also use qry, frm, rpt, etc. etc. Seems to me that having tbl as the start of every table name would make getting the table name a whole lot easier for when a query definition has no fields collection - unless you are trying to pair fields with tables as well. Beyond manually opening each query and determining those relationships, I don't know what else I'd do.

 

[jasmith4]

First, I agree with Hungarian notation, i.e., naming every table "tblXXX", etc., but others hate it.

Anyway, my situation is that I have to thoroughly explore any Access database that comes my way. So I've written lots of routines into an Access add-in that do what the Database Documenter does and much more, e.g., trace the complete dependency tree of any table or query field by field.

So I want something that can tell me about any make-table query:
- what's the target table, even if it never existed, and where does each of its fields come from?
- what other queries and tables does it depend on? (That I can do by examining the Fields collection.)
- one more level down, document each one of those tables and queries.

(Of course I want exactly that for an append or update query, but the advantage is that the target tables exist.)

What I have is a comprehensive and recursive system which does all this, even properly documenting links to Oracle and Excel tables, but documenting the target of a make-table query has been a sticking point.

 

[xenou]

Now, what about update queries, which can update more than one table???

- Update: UPDATE tables/queries SET field=expr, field=expr...

Update queries can only update a single table and it is the first one in the list.

Edit
I stand corrected: Access does allow you to update two tables.

UPDATE 
    Table1 
INNER JOIN 
    Table2 
    ON Table1.ID1 = Table2.ID1 
SET 
    Table1.Field1 = "foo", 
    Table2.Field1 = "bar"

So you'd have to parse the set clause and match table names (or aliases) and the respective fields.

 

[jasmith4]

I find also that you can append to two tables at once as well. Now, here's something tricky:

You have two related source tables, and the target table has all the fields in Source1 plus a few in Source2. In Design view you pull in and join the two sources, double-click Source1.*, and set "Append To" to Target.*. Then double-click a few in Source2 that are also in Target, say Field1 and Field2, and most likely "Append To" will self-populate.

The SQL is:
INSERT INTO Target.* (Field1, Field2) SELECT Source1.*, Source2.Field1, Source2.Field2 FROM Source1 LEFT JOIN Source2 ON (whatever)

Now, go back to Design View, and blank out the Append To cell that reads "Target.*", and now the SQL is:
INSERT INTO Target.* (Field1, Field2) SELECT Source2.Field1, Source2.Field2 FROM Source1 LEFT JOIN Source2 ON (whatever)

So it seems somehow I have to compare lists of fields from Target and Source1 in this case!

What would be easier is to somehow convert any query into a SELECT query, i.e., programmatically change its type to SELECT, as a user manually does when clicking the ribbon tools, then create a temporary query, per Micron's original idea, then get the list of fields and deal with anything called Exprnnnn.

I've also tried the SaveAsText method, but there's nothing useful in all that crap...