从查询表中获取列名 [英] 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 & "' "

它有效......

但是在执行 VBA 代码期间，我还需要不从表中检索列名，而是从查询后生成的表中检索列名，我如何在 VBA 中获取它?

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' 

它什么都不返回，因为表 MrgT 不在 INFORMATION_SCHEMA_COLUMNS 中，与其他表不同.

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

推荐答案

要从 QueryDef 获取列名，您可以使用 Fields 集合:

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

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

或者，您可以使用纯 SQL 和 MSysObjects 和 MSysQueries 系统表.有关此表结构的详细信息，请参阅此处.

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

这篇关于从查询表中获取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！