No data from OpenSchema

S

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
 
G

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
 
S

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
 
S

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
 
D

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

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

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
Click to expand...

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.

--
 
J

Jamie Collins

Record count returns -1 on any recordset which does not
return record counts -- which is most recordsets.
Click to expand...

I get a +ve RecordCount with OpenSchema because my
Connection object uses a client-side cursor (which is
common to all my connections said:
this works on my Access database,referencing ADO 2.7:
?CurrentProject.Connection.OpenSchema (adSchemaColumns,array(null,null,"MSysA
ccessObjects")).fields(3)
Click to expand...

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.

--
 
S

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

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

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

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

david epsom dot com dot au

final row that returns 'ID'). So what is this telling me
or supposed to be telling me?
Click to expand...

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

david epsom dot com dot au

Jamie Collins said:
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.
Click to expand...

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

(david)
 
J

Jamie Collins

david epsom dot com dot au said:
specifically, that the first column of the table
"MSysAccessObjects" is called "DATA".
Click to expand...

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

Thanks,
Jamie.

--
 
J

Jamie Collins

david epsom dot com dot au said:
No error here: the code works correctly (returning a record
count of -1)
Click to expand...

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.

--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

GetOleDbSchemaTable(OleDbSchemaGuid.Columns,... 11
SQL Server SP equivalent 5
"data type mismatch" when printing report from form 3
Garbage in a string variable loaded from a field 2
Problem retrieving data from a subform 1
VBA to delete Duplicate Records (1 column), before which, non-duplicate data merged into remaining r 6
Adding data from code turns into geometric progression 2
Pivot Table From MS Query 0

Top