Link to home
Start Free TrialLog in
Avatar of RayT
RayTFlag for United States of America

asked on

Access Schema

Is there a way to retrieve a database schema using the AccessDataSource and Visual Basic?

I only want to retrieve the table names.

Thanks
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

you can use the mSysObjects table, which is a hidden system table.

SELECT [Name] as TableName , [Database], [Type] FROM mSysObjects
WHERE [Type] = 6
OR [Database] is not NULL

I think there is another type that is involved here besides 6, but don't remember what it is right off the top of my head.  6 will get you built-in tables, if you have linked tables, you might want to check what the type is associated with them, which is what the 2nd criteria above will do for you.
ASKER CERTIFIED SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you use ADO with the JET OLEDB provider, you can use the DBSCHEMA_TABLES rowset.  

There's also getoledbschematable:

https://support.microsoft.com/en-us/help/309488/how-to-retrieve-schema-information-by-using-getoledbschematable-and-visual-basic-.net

or you can set a reference (if you did mean VB and not  VB.Net)  to the DAO lib and use the tabledef's collection.

But if your already reading the DB, then you can use's Dale's back door method as well.

Jim.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of RayT

ASKER

Thanks