I am trying to obtain the column names that form a primary key.
I receive the above error when trying to access the Indexes.Columns collection:
[VBA]Private Sub CommandButton1_Click()
ADOXtest
End Sub
Public Sub ADOXtest()
Set DBConn = New Connection
DBConn.Open "Test Access 2003 Database" ' <- DSN name
'Add project reference ADOX library:
'Microsoft ADO Ext. 2.8 for DDL and Security.
Dim cat As New ADOX.Catalog
Dim tblADOX As New ADOX.Table
Dim idxADOX As New ADOX.Index
Dim colADOX As New ADOX.Column
cat.ActiveConnection = DBConn
On Error GoTo errHandler
For Each tblADOX In cat.Tables
If tblADOX.Indexes.Count <> 0 Then
For Each idxADOX In tblADOX.Indexes
With idxADOX
If .PrimaryKey Then
For Each colADOX In .Columns ' << ERROR here
Debug.Print colADOX.Name
Next
End If
End With
Next
End If
Next
Set cat = Nothing
Set tblADOX = Nothing
Set idxADOX = Nothing
Set colADOX = Nothing
Exit Sub
errHandler: MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
Set cat = Nothing
Set tblADOX = Nothing
Set idxADOX = Nothing
Set colADOX = Nothing
End Sub
[/VBA]
The above code can be pasted into worksheet code.
I get the same error with Access and SQL Server
Can anyone help me - it is driving me nuts!
Reply With Quote