determining tables in Access with SQL

[rpriddy]

I have quite a bit of experience writing code to deal with databases, though most of that experience deals with Sybase and Oracle. I'm trying to write a bit of code to pull values out of an Access database and place them into a Sybase database. This all has to be generic though. One of the things i want to be able to do is give a list of all the tables in a given Access database in order to match them up with tables in Sybase. All i have to do in Sybase is do a SELECT on sys.systables. Access doesn't really seem to have the same kinda thing though. Can anyone help me out here? Thanks...

[VorTechS]

The best method of finding the tables in an MS Access database is to cycle through the tabledefs of your database object.

So, code something a little similar to:

Dim dbMyDatabase as database
Dim defMyQueryDefs as Querydef

Set dbMyDatabase = opendatabase(<Path_to_database_including_its_name>)

for each defMyQueryDefs in dbmydatabase.querydefs
debug.print defMyQuerydefs.name
next

Or something along those lines......

[Gaffer]

The above method will work (if you use Tabledefs instead of Querydefs!) - this is OK if you can still have the ability to use DAO. ADO is a different matter (you have to utilise ADOX)

Your other optio is to goto Tool...Options in Access and under the View tab, select system objects. This gives you a bunch of tables prefixed with MSys. MSysObjects give you a list of object in the database, including tables, which can be filtered out acccording to type (last column - -i think tables are type "1", linked tables type "4" etc)

[rpriddy]

I actually already tried to access the MSysObjects table using code, and it told me i didn't have the rights to do that. How should i connect in order to get the rights to read the rows in that table?

[Gaffer]

Yes, of course, sorry I tried it in Access using VBA. Won't work in VB

Best solution is using Tabledefs. If you are using Access 2000, you'll may want to try using ADOX.

[VorTechS]

Ooops sorry, I thought about it when I went home last night and realised I meant TableDefs not querydefs

[Gaffer]

Thats OK, I messed it up too...