Avatar of RayT
RayT
Flag 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
Microsoft AccessVisual Basic.NET

Avatar of undefined
Last Comment
RayT

8/22/2022 - Mon
Dale Fye

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
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Dettman (EE MVE)

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
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RayT

ASKER
Thanks
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck