检索Access数据库中的索引列表 [英] Retrieve list of indexes in an Access database
问题描述
我知道有一种方法可以通过使用MsysObjects查询Access数据库中的所有表的列表:
I know there's a way to get a list of all tables in an Access database by using the quering the MsysObjects:
SELECT MSysObjects.Name FROM MsysObjects
WHERE
Left$([Name],1)<>'~' AND
Left$([Name],4)<>'Msys' AND
MSysObjects.Type=1
有人知道类似可以检查 TableDef中的所有索引的列表?
Does anybody know a similar (or other) way to retrieve a list of all indexes in an MS-Access database?
推荐答案
对象访问索引名称。
You can examine TableDef
objects to access the index names.
Public Sub ShowIndexNames()
Dim tdf As TableDef
Dim idx As Index
Dim num_indexes As Long
On Error GoTo ErrorHandler
For Each tdf In CurrentDb.TableDefs
num_indexes = tdf.Indexes.Count
If Left$(tdf.Name, 4) <> "MSys" Then
If num_indexes > 0 Then
For Each idx In tdf.Indexes
Debug.Print tdf.Name, idx.Name
Next idx
End If
End If
Next tdf
ExitHere:
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3110
'Could not read definitions; no read definitions '
'permission for table or query '<Name>'. '
Debug.Print "No read definitions permission for " _
& tdf.Name
num_indexes = 0
Resume Next
Case Else
Debug.Print Err.Number & "-> " & Err.Description
GoTo ExitHere
End Select
End Sub
修改:修改子以忽略MSys *(访问系统)表。
Edit: Revised the sub to ignore MSys* (Access system) tables.
您还可以使用ADO的 OpenSchema
方法来检索索引的相关信息。下面的代码列出了索引名称,关联表以及索引是否是主键。我写了它为ADO使用晚期绑定,因为不需要为Microsoft ActiveX数据对象[版本]库设置引用。
You could also use ADO's OpenSchema
method to retrieve information about indexes. The code below lists the index name, associated table, and whether the index is the primary key. I wrote it to use late binding for ADO because that doesn't require setting the reference for Microsoft ActiveX Data Objects [version] Library.
Const adSchemaIndexes As Long = 12
Dim cn As Object ' ADODB.Connection
Dim rs As Object ' ADODB.Recordset
Dim i As Long
Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaIndexes)
With rs
' enable next three lines to view all the recordset column names
' For i = 0 To (.Fields.Count - 1)
' Debug.Print .Fields(i).Name
' Next i
Do While Not .EOF
Debug.Print !TABLE_NAME, !INDEX_NAME, !PRIMARY_KEY
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cn = Nothing
如果您喜欢检查单个表的索引,而不是数据库中的每个表,请将表名作为数组的第五个元素。
If you prefer to examine indexes for a single table rather than for every table in the db, pass the table name as the fifth element of an array.
Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblFoo"))
这篇关于检索Access数据库中的索引列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!