OpenSchema

[;-\)]

I am using ADO Access 2000 & VB 6.

This returns all table names and ALMOST all query names.

Set rsTbl = db.OpenSchema(adSchemaTables)
....
rsTbl!TABLE_NAME: rsTbl.MoveNext



The one it doesn't is a Union query. Does any one have any ideas
how to get even this name?

 

[M.L. Sco Scofield]

Some Jet queries (union, crosstab, etc.) get classified as stored procedures
when accessed using ADO.

Use the adSchemaProcedures constant for OpenSchema and PROCEDURE_NAME for
the field name.

Be aware, this also returns all of the system queries.

Although ADO is the "universal" data access library, it is optimum for
non-Jet data sources. When using Jet data sources, DAO is still the best. If
you were using the DAO QueryDefs collection, you would see all of the
queries.

Also, if you are using VB 6.0 and ADO with an mdb file, you are *not* using
Access. You are access the Jet data portion of the mdb file. For VB 6.0
questions, you will be better served by one of the 36 VB groups that all
start with microsoft.public.vb. For data access, your best bets would be
microsoft.public.vb.database, microsoft.public.vb.database.ado, and
microsoft.public.vb.database.dao.

Good luck.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[;-\)]

Thanks.

I switched long ago to ADO and will not go back. I did create a "SELECT *
FROM myUnion;" Query so I could see that name and do what I wanted.

I was doing a
rsTbl.Filter = "[TABLE_NAME] LIKE 'rptI_*'"
to return only my special report querys.
I will add a Excel sheet named with the query's name and place the results
on the new sheet.
This way I can add and change my reports without a recompile of the VB code.

Yes, that did return just the Union query name, but since it didn't have my
other report names I can't use it.


Microsoft has a habit of leaving out one level of detail in almost all help
files. They didn't explain what those options did. They also have a habit
of having one functional defect in most features too.

I can't wait until I try and find all the .NET bugs.

 

[___]

The following query will list even union queries.

SELECT Name
FROM MSysObjects
WHERE Type = 5;


HT

 

[M.L. Sco Scofield]

Inline...

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

Thanks.

I switched long ago to ADO and will not go back. I did create a "SELECT *

Right tool for the job. I'd never use my new power screwdriver to tighten
the screws on my eye glasses. For non-Jet sources, ADO is the right tool.
Even after years of pushing ADO down our throats, MS recommends DAO for Jet
again.

Besides, if ADO was so great, why did they totally rewrite it for .Net? The
only thing ADO and ADO.Net have in common are the three letters, "A," "D,"
and "O."

FROM myUnion;" Query so I could see that name and do what I wanted.

I was doing a
rsTbl.Filter = "[TABLE_NAME] LIKE 'rptI_*'"
to return only my special report querys.
I will add a Excel sheet named with the query's name and place the results
on the new sheet.
This way I can add and change my reports without a recompile of the VB code.

Yes, that did return just the Union query name, but since it didn't have my
other report names I can't use it.


Microsoft has a habit of leaving out one level of detail in almost all help
files. They didn't explain what those options did.

I'm by no means an ADO expert. Where do you think I found this information?
The help files...

They also have a habit
of having one functional defect in most features too.

I can't wait until I try and find all the .NET bugs.

Well, there's always Delphi...

 

[;-\)]

what????

Inline...

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

Thanks.

I switched long ago to ADO and will not go back. I did create a "SELECT

*

Right tool for the job. I'd never use my new power screwdriver to tighten
the screws on my eye glasses. For non-Jet sources, ADO is the right tool.
Even after years of pushing ADO down our throats, MS recommends DAO for Jet
again.

Besides, if ADO was so great, why did they totally rewrite it for .Net? The
only thing ADO and ADO.Net have in common are the three letters, "A," "D,"
and "O."

FROM myUnion;" Query so I could see that name and do what I wanted.

I was doing a
rsTbl.Filter = "[TABLE_NAME] LIKE 'rptI_*'"
to return only my special report querys.
I will add a Excel sheet named with the query's name and place the results
on the new sheet.
This way I can add and change my reports without a recompile of the VB code.

Yes, that did return just the Union query name, but since it didn't have my
other report names I can't use it.


Microsoft has a habit of leaving out one level of detail in almost all help
files. They didn't explain what those options did.

I'm by no means an ADO expert. Where do you think I found this information?
The help files...

They also have a habit
of having one functional defect in most features too.

I can't wait until I try and find all the .NET bugs.

Well, there's always Delphi...

 

[;-\)]

Thanks I will try this even tho I found a work-a-round.

Where in the Help did you find this?