Looping through tables in an another mdb file 1

[teach314]

here is some code to loop through the tables in the current db.

   Dim db As DAO.Database
   Dim tdf As DAO.TableDef
   Set db = CurrentDb
   For Each tdf In db.TableDefs
      If Not (tdf.name Like "MSys*" Or tdf.name Like "~*") Then
         Debug.Print tdf.name
         
         ... DO STUFF HERE ...

      End If
   Next
   Set tdf = Nothing: Set db = Nothing

I want to write a similar function, but I want to list the tables in another mdb file on an external drive.
The full Path and Name of the external file is in the variant varPathName....

eg: varPathName = "E:\2012_ARCHIVE\Semester_1\CurriculumGuides.mdb".
From the mdb file I'm currently running, I want to loop through the tables in varPathName.

I assume I need to change the code line "Set db = CurrentDb" to something else so that the code looks at the TableDefs in the external mdb file, but I can't get the syntax right. Any hints?

Thanks in advance

 

[dhookom]

I think it's just a matter of changing this

Set db = CurrentDb

To

Set db = OpenDatabase(varPathName)

Duane
Hook'D on Access
MS Access MVP

 

[teach314]

exactly what was needed. Thanks!

 

[georgesOne]

... and how do I do it with an URL...? e.g
Set db = OpenDatabase("

http://gft.fm.net/ICML/AccessDB2/RFDataBase/DataBases/Database6.accdb")

gives an error.
Best, georgesOne

 

[dhookom]

I doubt you can connect to an Access database file on a web server like this.

BTW: always include the complete error message rather than "gives an error".

Duane
Hook'D on Access
MS Access MVP