StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You can examine <code>TableDef</code> objects to access the index names.</p> <pre class="lang-vb prettyprint-override"><code>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 </code></pre> <p><strong>Edit</strong>: Revised the sub to ignore MSys* (Access system) tables. </p> <p>You could also use ADO's <code>OpenSchema</code> 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 <em>Microsoft ActiveX Data Objects [version] Library</em>. </p> <pre class="lang-vb prettyprint-override"><code>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 </code></pre> <p>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.</p> <pre class="lang-vb prettyprint-override"><code>Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "tblFoo")) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload