determining tables in Access with SQL

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...

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......

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)

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?

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

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

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

Thats OK, I messed it up too... :o