--001636498b65c5e2920486bb5ad5
Content-Type: text/plain; charset=ISO-8859-1
Hi,
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
--001636498b65c5e2920486bb5ad5
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
<div>Hi,<br>=A0<br>I have some standard code below that seems to work fine =
with all databases I have tested including teradata, oracle, sql server etc=
..</div>
<div>=A0</div>
<div>All I am trying to do is return a record=A0set object to the data in a=
table.<br>=A0<br>I've just installed and tested it on postgres and it =
gives me an error -<br>=A0<br>'object or provider is not capable of per=
forming requested operation'<br>
=A0<br>can anyone give me any clues as to what I am missing?<br>=A0<br>The =
code below can be tested in excel if you add a reference to ADO.<br>=A0<br>=
Public Sub test()<br>=A0=A0=A0 'connect<br>=A0=A0=A0 Dim cnSim As New A=
DODB.Connection<br>
=A0=A0=A0 Dim connectionstring As String<br>=A0=A0=A0 connectionstring =3D =
"Provider=3DPostgreSQL.1;Password=3Dpassword;User ID=3Dpostgres;Data S=
ource=3Dlocalhost;Location=3Dpostgres;Extended Properties=3D""&qu=
ot;""<br>=A0=A0=A0 cnSim.connectionstring =3D connectionstring<br=
>
=A0=A0=A0 cnSim.Open<br>=A0=A0=A0 <br>=A0=A0=A0 <br>=A0=A0=A0 'list the=
tables<br>=A0=A0=A0 Dim s As String<br>=A0=A0=A0 Dim s1 As String<br>=A0=
=A0=A0 Dim rs As New ADODB.Recordset<br>=A0=A0=A0 <br>=A0=A0=A0 Set rs =3D =
cnSim.OpenSchema(adSchemaTables)<br>=A0=A0 While Not rs.EOF<br>
=A0=A0=A0=A0=A0 s =3D rs!Table_Schema & "." & rs!TABLE_NA=
ME<br>=A0=A0=A0=A0=A0 s1 =3D rs!TABLE_NAME<br>=A0=A0=A0=A0=A0 Debug.Print s=
<br>=A0=A0=A0=A0=A0 rs.MoveNext<br>=A0=A0 Wend<br>=A0=A0=A0 <br>=A0=A0 '=
;works fine to here.<br>=A0=A0=A0 <br>=A0=A0=A0 'try to return record s=
et of a table<br>
=A0=A0=A0 Dim aRestrictions As Variant<br>=A0=A0=A0 aRestrictions =3D Array=
(Empty, Empty, s, Empty)<br>=A0=A0=A0 'aRestrictions =3D Array(Empty, E=
mpty, s1, Empty)<br>=A0=A0=A0 Set rs =3D cnSim.OpenSchema(adSchemaColumns, =
aRestrictions)<br>=A0=A0=A0 'Error - object or provider is not capable =
of performing requested operation'<br>
=A0=A0=A0 <br>End Sub</div>
--001636498b65c5e2920486bb5ad5--
