Get Column name from a query table

Question

With normal tables, I can access the column name using the

SELECT column_name FROM INFORMATION_SCHEMA_COLUMNS WHERE table_name = '" & Tbl & "' "

and it works...

but during the execution of VBA code I also need to retrieve columns name not from tables, but from the table resulting after a query, how can I get that in VBA?

Dim Qry as QueryDef
Dim MrgTbls as QueryDef
Dim T1 as String
Dim T2 as String
Dim SQLJoin as String

...

Set MrgTbls = CurrentDb.CreateQueryDef(Qry.Name, SQLJoin)

... and later on I want create a query such as

SELECT column_name FROM INFORMATION_SCHEMA_COLUMNS WHERE table_name = 'MrgT'

which returns nothing since the table MrgT is not in INFORMATION_SCHEMA_COLUMNS, unlike other tables.

Can you add more detail? What objects are you working with? E.g. rs.fields(i).name — Alex K., Jul 31 '13 at 14:14
As an aside, `Dim T1, T2, SQLJoin As String` defines two variables of type `Object` and only one of type `String`. Also, if `QryName` is of type `QueryDef`, how can you assign a string to `QryName`? — Zev Spitz, Jul 31 '13 at 14:55
How do you use `SELECT column_name FROM INFORMATION_SCHEMA_COLUMNS` on an Access database? — Zev Spitz, Jul 31 '13 at 15:04

score 1 · Accepted Answer · edited May 23 '17 at 12:34

1

To get the column names from a QueryDef, you can use the Fields collection:

Dim firstColumnName As String
firstColumnName = MrgTbls.Fields(0).Name

Alternatively you can use pure SQL and the MSysObjects and MSysQueries system tables. See here for details about the structure of this table.

edited May 23 '17 at 12:34

Community

1
1

answered Jul 31 '13 at 14:57

Zev Spitz

13,950
6
64
136

Get Column name from a query table

1 Answers1

Linked