adSchemaColumns

[petermeachem]

I am trying to list the columns in a selected table in a selected database. Sometimes this works and sometimes not, for apparently very similar databases. Can you see what I have done wrong, or is there a better way of doing it.

The code is :-
Set cnnnew = New adodb.Connection
cnnnew.ConnectionString = cLocalConnectionString
cnnnew.Open

Set rs = cnnnew.OpenSchema(adSchemaColumns)
If Not (rs.EOF And rs.BOF) Then
Do Until rs.EOF
If UCase$(rs(2)) = UCase$(cTable) Then
TListSource.AddItem rs(3)
TListSource.Indent(TListSource.ListCount - 1) = 0
End If
rs.MoveNext
Loop
End If
cnnnew.Close
Set cnnnew = Nothing

Where cTable is the table I want. The code fails on some databases on Set rs = cnnnew.OpenSchema(adSchemaColumns) and the error is
Object or provider is not capable of performing requested operation.
cLocalConnectionString is
Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=D:\VTCT\DATA\Users.mdb
I have two databases, one works and one doesn't. Both are linked to the same Access database, both contain queries, reports etc.

Any clues?
Peter Meachem
peter@accuflight.com

 

[petermeachem]

My good friend NickIsd has found the fix for this. The problem only seems to appear on Access 2000 databases which have been converted to Access97.

The crucial bit is changing the connection string from
cLocalConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & cFile

To

cLocalConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};dbq=" & cFile

Both the adSchema and Cat method work after that. Peter Meachem
peter@accuflight.com