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

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