Possible?? - Use DAO to loop though back end file properties

[Cosmos75]

Is there a way using DAO to loop through the back end files and get the path/file name infomation?

I know that I can accomplish the same thing by using a SQL string and recordset and parsing the 'Database' field

SELECT Database
FROM MSysObjects
GROUP BY Database, Type
HAVING Database Is Not Null AND Type=6;

What I am trying to accomplish is to loop through the tables but I want to loop through the table in order of the back end file. If I loop through the tabledefs using DAO I end up accessing the tables in alphabetical order of their name in the current database.

Dim thisDb As DAO.Database
Dim tdf As DAO.TableDef

Set thisDb = CurrentDb

For Each tdf In thisDb.TableDefs
    Debug.Print tdf.Name
    Debug.Print tdf.Connect
Next tdf

thisDb.Close

The way I am doing is now is by using a SQL string as a recordset.

SELECT Database, Connect, ForeignName, Name, Type
FROM MSysObjects
WHERE Name Not Like "MSys*" AND Type In (1,6)
ORDER BY Database, ForeignName;

Type = 1 returns local tables and there are not values for 'Database' or 'Connect' fields.

 

[Pat Hartman]

If you want only linked tables, use 4 (odbc) and 6 (jet). The order of tables in the tabledefs collection is creation order I think. Your query needs to be:

SELECT Database, Connect, ForeignName, Name, Type
FROM MSysObjects
WHERE Name Not Like "MSys*" AND Name Not Like "~*" AND Type In (6)
ORDER BY Database, ForeignName;