如何在MS Access中获取表的列名? [英] how to get column names of a table in ms access?

问题描述

可能的重复项:
使用SQL获取Microsoft Access表中的所有字段名称
MS Access 2010获取列名

Possible Duplicates:
Get all field names in Microsoft Access Table using SQL
MS Access 2010 Get column names

大家好，

如何使用查询获取msaccess中表的字段名或列名?

How to get fieldnames or columnname of a table in msaccess using query?

有人可以帮助我解决这个问题吗?

Can someone help me to overcome this issue?

就像我有一个名为"employee"的表一样，我需要仅获取雇员的字段名称(id，name，workstatus..etc)，而不是其记录...是否有任何方法可以获取此信息?

Like if i have table called "employee" i need to fetch the fieldnames(id,name,workstatus..etc) of employees alone not its records...Is any method to get this?

谢谢

推荐答案

以下是我过去用来跟踪审计等内容的一些代码.我对此不以为然，我在网络时代发现了它以前

Here is some code I have used in the past to keep track of things for audits etc. I cant take credit for it, I found it on the web ages ago

Sub GetField2Description()
'**********************************************************
'Purpose:   1) Deletes and recreates a table (tblFields)
'           2) Queries table MSysObjects to return names of
'              all tables in the database
'           3) Populates tblFields
'Coded by:  raskew
'Inputs:    From debug window:
'           Call GetField2Description
'Output:    See tblFields
'**********************************************************

Dim db As Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
n = 0
Set db = CurrentDb
' Trap for any errors.
    On Error Resume Next
tName = "tblFields"

'Does table "tblFields" exist?  If true, delete it;
docmd.SetWarnings False
   docmd.DeleteObject acTable, "tblFields"
docmd.SetWarnings True
'End If
'Create new tblTable
db.Execute "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55), FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"

strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"

Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
   ' Get number of records in recordset
   rs.MoveLast
   n = rs.RecordCount
   rs.MoveFirst
End If

Set rs2 = db.OpenRecordset("tblFields")

For i = 0 To n - 1
  fielddescription = " "
  Set td = db.TableDefs(i)
    'Skip over any MSys objects
    If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
       NameHold = rs!Name
       On Error Resume Next
       For Each fld In td.Fields
          fielddescription = fld.Name
          typehold = FieldType(fld.Type)
          SizeHold = fld.Size
          rs2.AddNew
          rs2!Object = NameHold
          rs2!FieldName = fielddescription
          rs2!FieldType = typehold
          rs2!FieldSize = SizeHold
          rs2!FieldAttributes = fld.Attributes
          rs2!FldDescription = fld.Properties("description")
          rs2.Update
       Next fld

       Resume Next
    End If
    rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub

Function FieldType(intType As Integer) As String

Select Case intType
    Case dbBoolean
        FieldType = "dbBoolean"    '1
    Case dbByte
        FieldType = "dbByte"       '2
    Case dbInteger
        FieldType = "dbInteger"    '3
    Case dbLong
        FieldType = "dbLong"       '4
    Case dbCurrency
        FieldType = "dbCurrency"   '5
    Case dbSingle
        FieldType = "dbSingle"     '6
    Case dbDouble
        FieldType = "dbDouble"     '7
    Case dbDate
        FieldType = "dbDate"       '8
    Case dbBinary
        FieldType = "dbBinary"     '9
    Case dbText
        FieldType = "dbText"       '10
    Case dbLongBinary
        FieldType = "dbLongBinary" '11
    Case dbMemo
        FieldType = "dbMemo"       '12
    Case dbGUID
        FieldType = "dbGUID"       '15
End Select

End Function

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