我知道这是获取数据库中所有表的列表的方法(如果修改标准,则会获取更多表的列表:
SELECT *
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0));
但是,是否有类似的方法来获取字段列表以及它们所属的表?我认为这对于故障排除和快速审核非常有用(即回答“此字段或字段组在哪些其他表中使用”)。但无论出于什么原因,都无法在网上找到答案。
如果有人有通过 VBA 实现的相对简单的方法,那也是一个选择。谢谢。
答案1
不幸的是,字段名称无法通过简洁的 SQL 查询获得。此代码将在调试窗口中的单独一行上打印表名称和每个字段。
Private Sub ShowTableFields()
Dim db As Database
Dim tdf As TableDef
Dim x As Integer
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then ' Don't enumerate the system tables
For x = 0 To tdf.Fields.Count - 1
Debug.Print tdf.Name & "','" & tdf.Fields(x).Name
Next x
End If
Next tdf
End Sub
答案2
在这种特殊情况下,您可能会发现 ADO Schemas 很有用。
Microsoft ActiveX Data Objects x.x Library
这将列出特定表的字段和一些属性。请注意数据类型,您可能无法获得与 DAO 类型完全匹配的数据。除非使用后期绑定,否则您将需要引用。
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaTables, _
Array(Empty, Empty, Empty, "tablenamehere"))
While Not rs.EOF
Debug.Print rs!table_name; " desc= "; rs!Description
Set rs2 = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, "" & rs!table_name & ""))
While Not rs2.EOF
Debug.Print " " & rs2!Column_Name
Debug.Print " " & rs2!Data_Type
Debug.Print " " & rs2!Description
Debug.Print " " & rs2!Is_Nullable
rs2.MoveNext
Wend
rs.MoveNext
Wend
rs.Close
Set cn = Nothing
您还可以反过来查看事物并获取包含特定字段的表的列表。
Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Dim strTempList As String
On Error GoTo Error_Trap
Set cn = CurrentProject.Connection
'Get names of all tables that have a column called <SelectFieldName>
Set rs = cn.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, Empty, SelectFieldName))
'List the tables that have been selected
While Not rs.EOF
'Exclude MS system tables
If Left(rs!Table_Name, 4) <> "MSys" Then
strTempList = strTempList & "," & rs!Table_Name
End If
rs.MoveNext
Wend
ListTablesContainingField = Mid(strTempList, 2)
从:http://wiki.lessthandot.com/index.php/ADO_Schemas
Stackoverflow 上有很多关于这个主题的内容:https://stackoverflow.com/search?q=%5Bms-access%5D+schema