No data from OpenSchema

[Sam Hobbs]

As best as I understand from the documentation and these newsgroups, the
code below should work. However I am getting an empty recordset (both BOF
and EOF are true). I am just writing out a table's field information using
OpenSchema.

If this is not the correct newsgroup for this, then you can just tell me
where to go.


Sub TestShowTable()
Open "TableSchema.txt" For Output As #1
ShowTable CurrentProject.Connection, "Table1"
Close #1
End Sub

Public Sub ShowTable(Connection, TableName)
Dim rstSchema As New ADODB.Recordset
Dim Criteria(3) As Variant
Criteria(0) = Empty ' TABLE_CATALOG
Criteria(1) = Empty ' TABLE_SCHEMA (owner)
Criteria(2) = TableName ' TABLE_NAME
Criteria(3) = Empty ' COLUMN_NAME
Set rstSchema = Connection.OpenSchema(adSchemaColumns, Criteria)
If rstSchema.RecordCount <= 0 Then
MsgBox "No table or no fields in table"
End If
Do While Not rstSchema.EOF
Record = rstSchema!TABLE_NAME
Record = Record & vbTab & rstSchema!ORDINAL_POSITION
Record = Record & vbTab & rstSchema!COLUMN_NAME
Record = Record & vbTab & rstSchema!DATA_TYPE
Record = Record & vbTab & rstSchema!CHARACTER_MAXIMUM_LENGTH
Record = Record & vbTab & rstSchema!NUMERIC_PRECISION
Print #1, Record
rstSchema.MoveNext
Loop
rstSchema.Close
End Sub

 

[Graham R Seach]

Sam,

You need to use an array:
Array(TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE)

So what you'd end up with is:
Set rstSchema = Connection.OpenSchema(adSchemaColumns, Array(Empty, Empty,
"Criteria(2), Criteria(3))

Personally, I'd do away with Criteria(), and just use variables.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Sam Hobbs]

Thank you, Graham, but I think that is more of a guess. Doesn't the Dim
statement create an array when the bound (subscript) is specified?

The code I am using is a modifed version of my own code that I wrote to do
something else. I am nearly certain that the portion you are commenting on
has not been modifed. Regardlous, I tried your suggestion and the code
behaves the same.

Also, "Array" is not used in the "Command3_Click" Sub in:

How To Use the ADO OpenSchema Method in Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;en-us;186246

 

[Graham R Seach]

Sam,

Since you already have the Catalog specified in the connection string, set
criteria(0) = Empty.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Sam Hobbs]

Even with this small peice of code, I get -1 for the RecordCount. Even when
I try it in a different database. So is that an indication that my system is
messed up? I will try restarting my system, in case that makes a difference.


Public Sub Test()
Dim rstSchema As New ADODB.Recordset
Set rstSchema = CurrentProject.Connection.OpenSchema(adSchemaColumns)
MsgBox rstSchema.RecordCount
End Sub

 

[Sam Hobbs]

Restarting my system has no affect.

 

[david epsom dot com dot au]

Record count returns -1 on any recordset which does not
return record counts -- which is most recordsets. If the
recordset is known to be empty, it will have a recordcount
of 0, but in general, use .BOF and .EOF

FWIW this works on my Access database,referencing ADO 2.7:
?CurrentProject.Connection.OpenSchema(adSchemaColumns,array(null,null,"MSysA
ccessObjects")).fields(3)

Returning the value:
DATA


(david)

 

[Sam Hobbs]

I think I got the idea to check RecordCount because a (Microsoft) sample I
saw uses it. Regardlous, note that in my original code, my code would still
get results if threre were data in the recordset.

 

[Jamie Collins]

Even with this small peice of code, I get -1 for the
RecordCount. Even when

I try it in a different database. So is that an indication that my system is
messed up? I will try restarting my system, in case that makes a difference.


Public Sub Test()
Dim rstSchema As New ADODB.Recordset
Set rstSchema = CurrentProject.Connection.OpenSchema (adSchemaColumns)
MsgBox rstSchema.RecordCount
End Sub

With this code I would get a run-time error telling me the
provider (OLE DB provider for Jet 4.0) doesn't support
this operation. With Jet I find I must do it by table i.e.
supply the table name each time.

Jamie.

--

 

[Jamie Collins]

Record count returns -1 on any recordset which does not

return record counts -- which is most recordsets.

I get a +ve RecordCount with OpenSchema because my
Connection object uses a client-side cursor (which is

this works on my Access database,referencing ADO 2.7:
?CurrentProject.Connection.OpenSchema (adSchemaColumns,array(null,null,"MSysA
ccessObjects")).fields(3)

I tried this in using ADO 2.7 and the OLE DB Provider for
Jet 4.0 from *Excel* and got 'Data' (I have second and
final row that returns 'ID'). So what is this telling me
or supposed to be telling me?

Jamie.

--

 

[Sam Hobbs]

You are right that RecordCount is useless in this situation.

 

[Sam Hobbs]

Restarting my system probably did fix the problem. My use of RecordCount to
determine if it was working caused me to not realize that it was working
after I restarted.

 

[Sam Hobbs]

I am sorry. It seems you are probably correct. I sure don't understand why I
was confused. Your sample code using Array does work, and I somehow missed
seeing that it does. I was too quick to assume it does not work.

The sample code I was looking at that does not use Array is for VB not for
Access, so perhaps that is why it works that way but not for VB for Access.

I have been in this situation before, except with the roles reversed, where
someone is too quick to assume that my assistance does not work. I don't
remember anyone else in that situation ever admitting that they made a
mistake. I thank you for your patience and making it easy for me to admit I
made a mistake.

 

[Sam Hobbs]

Ooops, now my original version, without Array, is also working.

 

[Graham R Seach]

Sam,

Happens to me too. Perhaps the little dance you did was what did it! Or
maybe the swearing!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[Sam Hobbs]

I did not think anyone could hear me swearing.

I think it was the system restart. I have been having a variety of
relatively small problems, most of which seem unrelated. However as I
mentioned previously, use of the RecordCount probably misled me to thinking
it was not working even when it did work.

I hope this little summary will help others in case they have a similar
problem and get confused by all the other comments.

The reason I prefer not to use Array is that use of it makes a long line.

 

[david epsom dot com dot au]

final row that returns 'ID'). So what is this telling me

or supposed to be telling me?

It's telling you about the Schema of the Connection:

specifically, that the first column of the table
"MSysAccessObjects" is called "DATA".

Which is correct, and indicates that the syntax of the
method is correct, including the use of the criteria
parameter.

Use .fields(3).name instead of .fields(3).value to see
the column name for fields(3)

(david)

 

[david epsom dot com dot au]

With this code I would get a run-time error telling me the
provider (OLE DB provider for Jet 4.0) doesn't support
this operation. With Jet I find I must do it by table i.e.
supply the table name each time.

Jamie.

No error here: the code works correctly (returning a record
count of -1)

(david)

 

[Jamie Collins]

specifically, that the first column of the table
"MSysAccessObjects" is called "DATA".

I get it: it's a table you knew would be present in the database
because it's a system table.

Thanks,
Jamie.

--

 

[Jamie Collins]

No error here: the code works correctly (returning a record
count of -1)

You may have noticed I hadn't actually tried the code, I though I
*would* get an error. Well, now I have, even with your exact code in
the Access2003 UI (the only change I made was to change ADO 2.1 to ADO
2.7) and the run-time error is:

? err.Number
3251
? err.Description
Object or provider is not capable of performing requested operation.

I would like to be able get all columns for all tables in one hit
rather than do a adSchemaTables and plug each into adSchemaColumns but
I haven't been able to. Can you think of anything that I may have
different from you?

Thanks again,
Jamie.

--