Thread: ADO - adSchemaColumns doesn't seem to work.
Hi,
I have some standard code below that seems to work fine with all databases I have tested including teradata, oracle, sql server etc.
I have some standard code below that seems to work fine with all databases I have tested including teradata, oracle, sql server etc.
All I am trying to do is return a record set object to the data in a table.
I've just installed and tested it on postgres and it gives me an error -
'object or provider is not capable of performing requested operation'
can anyone give me any clues as to what I am missing?
The code below can be tested in excel if you add a reference to ADO.
Public Sub test()
'connect
Dim cnSim As New ADODB.Connection
Dim connectionstring As String
connectionstring = "Provider=PostgreSQL.1;Password=password;User ID=postgres;Data Source=localhost;Location=postgres;Extended Properties="""""
cnSim.connectionstring = connectionstring
cnSim.Open
'list the tables
Dim s As String
Dim s1 As String
Dim rs As New ADODB.Recordset
Set rs = cnSim.OpenSchema(adSchemaTables)
While Not rs.EOF
s = rs!Table_Schema & "." & rs!TABLE_NAME
s1 = rs!TABLE_NAME
Debug.Print s
rs.MoveNext
Wend
'works fine to here.
'try to return record set of a table
Dim aRestrictions As Variant
aRestrictions = Array(Empty, Empty, s, Empty)
'aRestrictions = Array(Empty, Empty, s1, Empty)
Set rs = cnSim.OpenSchema(adSchemaColumns, aRestrictions)
'Error - object or provider is not capable of performing requested operation'
End Sub
I've just installed and tested it on postgres and it gives me an error -
'object or provider is not capable of performing requested operation'
can anyone give me any clues as to what I am missing?
The code below can be tested in excel if you add a reference to ADO.
Public Sub test()
'connect
Dim cnSim As New ADODB.Connection
Dim connectionstring As String
connectionstring = "Provider=PostgreSQL.1;Password=password;User ID=postgres;Data Source=localhost;Location=postgres;Extended Properties="""""
cnSim.connectionstring = connectionstring
cnSim.Open
'list the tables
Dim s As String
Dim s1 As String
Dim rs As New ADODB.Recordset
Set rs = cnSim.OpenSchema(adSchemaTables)
While Not rs.EOF
s = rs!Table_Schema & "." & rs!TABLE_NAME
s1 = rs!TABLE_NAME
Debug.Print s
rs.MoveNext
Wend
'works fine to here.
'try to return record set of a table
Dim aRestrictions As Variant
aRestrictions = Array(Empty, Empty, s, Empty)
'aRestrictions = Array(Empty, Empty, s1, Empty)
Set rs = cnSim.OpenSchema(adSchemaColumns, aRestrictions)
'Error - object or provider is not capable of performing requested operation'
End Sub
On Sun, May 16, 2010 at 12:45 PM, Phil Brierley <philb@philbrierley.com> wrote: > All I am trying to do is return a record set object to the data in a table. > I've just installed and tested it on postgres and it gives me an error - > 'object or provider is not capable of performing requested operation' > can anyone give me any clues as to what I am missing? the code works for me. Could it be a problem with the connection string? I'm using: PostgreSQL 8.4.3 PostgreSQL-ODBC Driver 8.04.02 Microsoft ActiveX Data Objects Library 2.5 Public Sub test() 'connect Dim cnSim As New ADODB.Connection Dim connectionstring As String 'connectionstring = "Provider=PostgreSQL.1;Password=password;User ID=postgres;Data Source=localhost;Location=postgres;Extended Properties=""""" connectionstring = "DSN=broersr; UID=broersr; Database=p2082849b" cnSim.connectionstring = connectionstring cnSim.Open 'list the tables Dim s As String Dim s1 As String Dim rs As New ADODB.Recordset Set rs = cnSim.OpenSchema(adSchemaTables) While Not rs.EOF s = rs!Table_Schema & "." & rs!TABLE_NAME s1 = rs!TABLE_NAME Debug.Print s rs.MoveNext Wend 'works fine to here. 'try to return record set of a table Dim aRestrictions As Variant aRestrictions = Array(Empty, Empty, s, Empty) 'aRestrictions = Array(Empty, Empty, s1, Empty) Set rs = cnSim.OpenSchema(adSchemaColumns, aRestrictions) 'Error - object or provider is not capable of performing requested operation' While Not rs.EOF Debug.Print rs!COLUMN_NAME rs.MoveNext ' for some reason the recordset only return the xmin column for me before rs.eof = true Wend End Sub -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug