Working with .mdb files

[scripty21]

Hello all,

I am working in Excel using VBA and am extracting data from a .mdb file.

The .mdb file contains anywhere from 50-300 tables and the user needs to be able to choose which table(s) he/she wants to extract. I am doing a lot of processing of the data being extracted, and the shear number of tables involved means that this task has to be automated.

and here lies the rub: I do not know the table names.

I do know that each table will start with the same initial format. E.g. names will be of the form: "WBS_LC_TS_XXXXXXXXXX" Where X represents any alpha numeric character. Also the number of "X"s may change.

What I would ideally like to do is to find some way of interrogating the .mdb file to generate a list of all the tables contained within that file. I am not very familiar with the structure of .mdb files or with working with data exchange objects, so I'm not even sure what approaches to take. Here are a few of my ideas so far:

1: Use SQL? Can this be done using SQL? (E.g. something like: "SELECT tablenames FROM mythical_mdb_masterindex_list"

2. ADO. Can this be done using an ADO connection object?

3. Working directly from the .mdb file. The table names are stored somewhere in the .mdb file. If I knew the format of the file (work length, encoding type etc) I could open the file in binary and then search for the start of each table name.

Does anybody know of a way of doing this?

Thanks in advance for your help!

[daniel aristidou]

Hi this code should return a list of all the forms in the database....yes i know its not exactly what you want but.....i just can remember the correct itm number:
create a new query in ms access and use this code

SELECT MSysObjects.Name AS NameOfForm INTO tblMyListOfFormNames
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Name;

The number "-32768"is the incorrect number......... but hopefully maybe we can come up with the correct one

[scripty21]

Hi this code should return a list of all the forms in the database....yes i know its not exactly what you want but.....i just can remember the correct itm number:
create a new query in ms access and use this code

SELECT MSysObjects.Name AS NameOfForm INTO tblMyListOfFormNames
FROM MSysObjects
WHERE (((MSysObjects.Type)=-32768))
ORDER BY MSysObjects.Name;

The number "-32768"is the incorrect number......... but hopefully maybe we can come up with the correct one

Hi Daniel, thanks for your suggestion.

I tried to implement the above, but get an infamous error message: "No read permission on 'MSysObjects' "

I have been digging around and have found infomation indicating that the table names are stored in the metadata of the .mdb file. In VB6 and or .Net, there is a GetSchema method of a DbConnection object, but I haven't found an equivalent in VBA.

So there are two potential lines of attack now:

1. Use the MSysObjects if I can get around the read premissions and find the table name object type constant.

2. Find a way of accessing the metadata using something similar to the GetSchema method, but that can be done from excel VBA.

Any and all suggestions welcome!

Thanks!