"Jake Marx" wrote ...

I notice that you typically invoke the OpenSchema method to loop through
tables (instead of using ADOX). I haven't done any testing, but is your
method more efficient? I would guess there's some additional overhead
introduced by the ADOX objects, but I'm wondering if you know how much
difference there is....

I'm not sure about efficiency issues (we're both using late binding,
after all!). I suppose if you are already using ADO in your project
then not using ADOX means one less reference!

<Respect for MVPsAs I'm sure you are already aware,</Respect for
MVPs the main reason for using the OpenSchema method is it gives me a
recordset object, rather than a collection, to work with. So I can do
recordset type things with the results e.g. use its Filter property
and GetRows method to get a 2-D array, use the CopyFromRecordset
method, associate it with the DataSource property of an ActiveX
control, etc.

[Unfortunately, the Filter method isn't sophisticated enough for our
Excel sheets exercise because the wildcard character in a LIKE clause
must be at the end (or the beginning *and* end); also the dollar sign
is reserved for numeric data.]

With OpenSchema my top level object is not restricted to being a
table, of course. For example, if I'm searching all tables for columns
with 'ID' in their name, a flatter structure is more useful:

Set adRs = adCn.OpenSchema(4) ' adSchemaColumns
adRs.Filter = "COLUMN_NAME LIKE '%ID%'"
adRs.Sort = "TABLE_NAME"

BTW the above doesn't work with the Excel provider :-(

--