PDA

View Full Version : ADOX :Object or provider is not capable of performing requested operation



brigzy
09-10-2012, 09:31 AM
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:

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




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!:banghead:

brigzy
09-11-2012, 02:08 AM
I found that this problem occurs when using a DSN (ODBC) but it works if using a connection string (OLEDB).

Hope this helps anyone trying this.

Richard
leansoftware.net