CODEWITHSUNDEEP
ms-accessvba
MC-8
MC-8

Reputation: 65

Get Column name from a query table

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.

Upvotes: 1

Views: 7968

Answers (1)

Zev Spitz
Zev Spitz

Reputation: 15297

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.

Upvotes: 1

Related Questions