如何使用SQL在Access中列出表中字段的名称

Question

请问是否有可能在MS Access表中列出所有字段名称？

- Suffii

---

由于您提到了SQL，也许您想做类似于SQL Server的事情，比如Select object_Name(object_ID),definition from Pastries.SQL_Modules where object_Name(object_ID)='custard'。我相信Access没有提供这种对象可见性。 - Smandoli

Answer 1

我经常使用微软Access。

我所知道的唯一方法是使用VBA，例如定义一个记录集并循环遍历字段。

示例：

Sub ListFields()

dim rst as new adodb.recordset
rst.open "SELECT * FROM SomeTable", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
' Note: adOpenForwardOnly and adLockReadOnly are the default values '
' for the CursorType and LockType arguments, so they are optional here '
' and are shown only for completeness '

dim ii as integer
dim ss as string
for ii = 0 to rst.fields.count - 1
    ss = ss & "," & rst.fields(ii).name
next ii

Debug.Print ss

End Sub

字符串变量ss将包含名为"SomeTable"的表中所有列名以逗号分隔的列表。

通过对逻辑进行一些重新格式化，您应该能够将此数据插入到另一个表中，然后查询出来。

这有帮助吗？

- John Bingham

---

支持使用SQL（可能有点名义上的），并表明已经过分地在MS Access中工作的确凿证据。 - Smandoli

Access并不总是你需要的工具，但它经常是你手头拥有的工具。关于在Access 2013中应用此方法的一点提示：我必须在“工具”->“引用”中启用“Microsoft ActiveX Data Objects 6.1 Library”来解决ADO库。 - StockB

@John Bingham - 刚刚遇到了一个AccessDB的问题，不得不来查找这个 - 它让我免受了一些烦恼，也可能避免了一些沮丧的想法 :) 谢谢！ - SlimsGhost

好的，紧凑的方法，谢谢，并且已经测试过可以正常工作。我提出了一些额外的行来将这个VBA片段转换为一个子程序，使开发人员能够按需获得一次性输出（当然，'SomeTable'仍需要替换为正确的表名）。 - Dodecaphone

Answer 2

这个版本易于运行，并且可以直接粘贴到Access中。将此函数添加到模块中，按F5运行，然后从输入框中复制结果：

Public Function FieldNames() As String

    Dim sTable As String
    Dim rs As DAO.Recordset
    Dim n As Long
    Dim sResult As String

    sTable = InputBox("Name of table?")
    If sTable = "" Then
        Exit Function
    End If

    Set rs = CurrentDb.OpenRecordset(sTable)

    With rs
        For n = 0 To .Fields.Count - 1
            sResult = sResult & .Fields(n).Name & vbCrLf
        Next 'n
        .Close
    End With

    Set rs = Nothing

    InputBox "Result:" & vbCrLf & vbCrLf _
            & "Copy this text (it looks jumbled, but it has one field on each line)", _
            "FieldNames()", sResult

End Function

替代方案：

用户user1003916提供了一种替代InputBox的方法，以克服1024字符限制（我尚未测试）：

Sub CopyText(Text As String)

    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014

    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing

End Sub

- Don Jewett

---

很好用。它可以列出您输入的任何表中的所有字段。唯一令人困惑的地方是...它将结果放在那个小文本框里。我一开始以为它不工作，但它确实工作了。只需要将结果复制粘贴到Excel或Notepad++中即可。 - Shai

好的，Shai。我在最后的对话框中添加了一些文本，这样就不会那么令人困惑了。 - Don Jewett

脚本很棒，但请记住这里有1024个字符的限制。如果您有很多长列名，它将截断它们。我的表有255列，它只抓取了大约一半。 - cowsay

谢谢@user1003916。我已经采纳了你的建议。 - Don Jewett

Answer 3

更新：要使用此SQL查询，您必须使用诸如DBEAVER之类的工具。Access客户端将不允许您查询其隐藏结构。

天啊！在我看来，我无法想象想要潜入VBA的黑暗下腹。

如何通过SQL获取Access表列

SELECT * FROM information_schema.columns 
    WHERE TABLE_NAME="YOUR_TABLE_NAME" 
       AND 
    TABLE_SCHEMA="PUBLIC" 

我注意到Access把我的模式称为“PUBLIC”。

上述代码使用Access 2016，经过ODBC和jdbc:ucanaccess测试，其效果非常好。

示例输出

- David Lundquist

---

不确定为什么这个想法被投票否决了。这种技术效果很好。 - David Lundquist

我将此内容与我的表名等一起放入查询窗口中，结果显示找不到文件information_scheme.mdb。 - Pertinent Info

非常抱歉，我似乎忘记了一个基本的指令。您不能使用Access客户端在Access数据库上执行此查询。相反，您必须使用诸如DBeaver之类的工具。我不确定为什么，但是当在Access中运行查询时，情况会变得一团糟。当在dBeaver中运行相同的查询时，您将按预期获得结果。 - David Lundquist

请再仔细阅读我的说明，我已经尝试提供一个清晰的示例，如何使用纯SQL从访问数据库获取列名。 - David Lundquist

我没有给你点踩。我只是回复了一下 :) 看起来是个不错的工具。 - Pertinent Info

Answer 4

您可以使用文档生成器工具。转到数据库工具 > 数据库文档生成器，选择表并按下“确定”即可。

- Smandoli

---

我完全同意你的观点。虽然感觉这个答案可能有所帮助，但也许并不是最佳想法。 - Smandoli

我认为这不是最好的问题 :) - Fionnuala

虽然这样做是可行的，但它可能是你能得到的最不实用的格式。 - Steven Bitaxi

Answer 5

这个SQL语句适用于Access 2016中的查询，而不是表格，但可能会很有用。

SELECT MSysObjects.Name AS QueryName, 
       IIf(IsNull([Name1]),
           Right([Expression],Len([Expression])-InStr(1,[Expression],".")),[name1])
           AS FieldName
  FROM MSysQueries INNER JOIN MSysObjects 
    ON MSysQueries.ObjectId = MSysObjects.Id
 WHERE MSysQueries.Attribute=6;

- Jonathan176

---

谢谢，这是最简单的方法。 - Youssri Abo Elseod

Answer 6

一个快速而不太规范的方法是使用Excel。请按照以下步骤操作：

1. 在“数据表视图”中打开表。
2. 使用适用于您的Access版本的特定程序将其导出到Excel文件中。
3. 打开Excel文件（如果尚未打开）。
4. 选择并复制包含标题的第一行。
5. 向工作簿添加另一个工作表（如果不存在）。
6. 单击A1。
7. 粘贴特殊 > 转置

字段将被粘贴在单列中。要查找它们的字段索引号，请在单元格B1中键入“0”，然后向下填充到字段编号的最后一行。

此外，您可以按字母顺序对该列进行排序，特别是对涉及数十个字段的旧平面文件非常有用。当我试图将平面文件转换为关系模型时，这真的节省了很多时间。

- Michael Reese

---

然后将其粘贴到VSCode或类似的编辑器中，根据需要添加逗号、引号等。 - Murrah

Answer 7

已经有一些好的答案了，但我决定加入自己的想法。希望它们是不言自明的。

用法：

• getFieldNames(TableName:="Table1",IncludeBrackets:=True,Delimiter:=vbNewLine,CopyToClipboard:=True)
• getFieldNames(TableName:="Table1",IncludeBrackets:=True,CopyToClipboard:=True)
• getFieldNames(TableName:="Table1",IncludeBrackets:=True)
• getFieldNames(TableName:="Table1")

我使用这个来构建一个字段名数组：

• Chr(34) & getFieldNames(TableName:="Table1",IncludeBrackets:=False, Delimiter:= Chr(34) & "," & Chr(34)) & Chr(34)

---

Function getFieldNames(ByVal TableName As String, Optional ByVal IncludeBrackets As Boolean, Optional ByVal Delimiter As String = ", ", Optional ByVal CopyToClipboard As Boolean) As String
    Dim rs As DAO.Recordset

    On Error Resume Next
    Set rs = CurrentDb.OpenRecordset(TableName)
    On Error GoTo 0

    If rs Is Nothing Then Exit Function

    Dim results() As String
    ReDim results(rs.Fields.Count - 1)

    Dim n As Long
    For n = 0 To rs.Fields.Count - 1
        results(n) = rs.Fields(n).Name
    Next
    rs.Close

    Dim result As String
    If IncludeBrackets Then
        result = "[" & Join(results, "]" & Delimiter & "[") & "]"
    Else
        result = Join(results, Delimiter)
    End If


    If CopyToClipboard Then
        With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            .SetText result
            .PutInClipboard
        End With
    End If

    getFieldNames = result
End Function

- TinMan

Answer 8

这不是SQL，但对于像我这样只需要列出查询所需字段名称的人来说，这个问题是谷歌的首要结果，因为Access不支持“* except foo,bar”来获取表的99%。 答案改编自社区.msdn.com上Patrick Wood（Access MVP）的回答 https://social.msdn.microsoft.com/Forums/office/en-US/1fe5546b-db3f-4e17-9bf8-04f4dee233b7/how-to-list-all-the-field-names-in-a-specified-table?forum=accessdev 将tablename更改为模块中的名称。此函数应位于全局级别：

Function ListTdfFields()
    ' NOT doing DIMs, since then you must enable/attach ADODB or DAO
    ' Dim db As ADO.Database
    Set db = CurrentDb
    tablename = "tblProductLicense"  ' <=== YOUR TABLE NAME HERE
    Set tdf = db.TableDefs(tablename)
    For Each fld In tdf.Fields
        Debug.Print tablename; ".["; fld.Name; "]," ; 
        ' remove ending ; for 1 line per field
    Next
    Debug.Print ""
    Set tdf = Nothing
    Set db = Nothing
End Function

然后添加一个宏RunCode ListTdfFields()并运行它。输出将被发送到模块的VBA设计视图的Immediate窗口。

- Dave S

Answer 9

不确定为什么没有人提出以下解决方案，我遇到了同样的问题，上面的答案要么无效，要么不适用（没有VB，谢谢）。 这是一个简单的Java例子（省略了错误检查等）。

String query="SELECT TOP 1 * FROM mytable";
ResultSet rs=connectionSQL.createStatement().executeQuery(query);
ResultSetMetaData rsm=rs.getMetaData();
int colcount=rsm.getColumnCount();
for(int f=1; f<=colcount; f++) {
    String coltype=rsm.getColumnTypeName(f);
    String colname=rsm.getColumnName(f);
    .... (whatever)
}

所以这就是你的列名和数据类型。 在MSACCESS中运行得很好。

- user3211098

Answer 10

我来到这里寻找同样的需求，参考了这个帖子后，为我的需求起草了下面的代码。源表中的字段名将被添加到一个数组中，然后这些字段名将被分配给第二个表。在这里分享一下，这可能会帮助以后的某个人。

Public Sub FieldName_Change()
Dim intNumberOfFields, intX As Integer
Dim txtTableName,txttmpTableName txtFieldName() As String

intNumberOfFields = GetFieldNames(txtTableName, txtFieldName())
For intX = 1 To intNumberOfFields
  CurrentDb.TableDefs(txttmpTableName).Fields("F" & intX).Name = txtFieldName(intX)
Next intX
End Sub


Public Function GetFieldNames(ByVal txtTableName As String, ByRef txtFiledName() As String) As Integer
Dim rs As DAO.Recordset
Dim n As Long
Dim sResult As String

Set rs = CurrentDb.OpenRecordset(txtTableName)
ReDim txtFiledName(rs.Fields.Count)
With rs
    For n = 0 To .Fields.Count - 1
        txtFiledName(n + 1) = .Fields(n).Name
    Next n
    .Close
    GetFieldNames = n
End With
Set rs = Nothing
End Function

- Oki Toma