ADO - adSchemaColumns doesn"t seem to work.

--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

Re: ADO - adSchemaColumns doesn"t seem to work.

On Sun, May 16, 2010 at 12:45 PM, Phil Brierley wr=
ote:

> All I am trying to do is return a record=A0set object to the data in a ta=
ble.
> 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 =3D "Provider=3DPostgreSQL.1;Password=3Dpassword;User
ID=3Dpostgres;Data Source=3Dlocalhost;Location=3Dpostgres;Extended
Properties=3D"""""
connectionstring =3D "DSN=3Dbroersr; UID=3Dbroersr; Database=3Dp208284=
9b"

cnSim.connectionstring =3D connectionstring
cnSim.Open


'list the tables
Dim s As String
Dim s1 As String
Dim rs As New ADODB.Recordset

Set rs =3D cnSim.OpenSchema(adSchemaTables)
While Not rs.EOF
s =3D rs!Table_Schema & "." & rs!TABLE_NAME
s1 =3D 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 =3D Array(Empty, Empty, s, Empty)
'aRestrictions =3D Array(Empty, Empty, s1, Empty)
Set rs =3D 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 =3D true
Wend


End Sub

--=20
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--=20
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc