MS Access VBA Get All External Access Database Tables for versions 2000, 2002, 2003, 2007

Go here To Get All Tables Table in a Current Database in VBA.
Go here To Update a Field in VBA.
Go here To Get all Fields in a Table with VBA

The VBA Get All External Access Database Tables Sample is available for download here in a zipped MS Access Database. The below picture (click to enlarge) shows the Single form and module.

MS Access VBA Get All Tables in an External database.

The following code snippet will use VBA to Return all Table Names within an External Access database.

Public Sub GetAllExternalTables(spath As String , strDB As String , lst As ListBox)

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   ' Returns all Table Names that exist in an External MS Access Database.
   ' Accepts
   ' spath: Path to External MS Access Database
   ' Returns All your Table Names in an External MS Access Database
   ' and excludes tables with MSys and ~
   'USAGE: GetAllExternalTables "spath"

   On Error GoTo errhandler 'Capture any errors.
   Dim i As Integer , X As Integer , numTables As Integer
      Dim tabName As String , strPath As String

   If Len(spath) > 0 Then
      If Left(spath, 1 ) <> "\" Then
         spath = "\" & spath
         If Right(spath, 1 ) <> "\" Then
            spath = spath & "\"
         End If
      End If

   Else
      spath = "\"
   End If

   'Set import Path
   strPath = CurrentProject.path & spath

   'Ensure we have not made double "\" anywhere.
   strPath = Replace(strPath, "\\" , "\" )

   'complete the path string
   strPath = strPath & strDB

   ' Loop through all the Tables Clear List
   With lst
      .RowSourceType = "Value List"
      For X = 0 To .ListCount - 1

         If .ListCount - 1 > 0 Then

            If IsNull(.Column( 0 , X)) = False Then
               .RemoveItem .Column( 0 , X)
            End If

            If IsNull(.Column( 0 , X)) = True Then X = 0
         Else
            .RemoveItem .Column( 0 , 0 )
            Exit For
         End If
      Next
   End With
   'Get Number of External Tables
   numTables = DBEngine.Workspaces( 0 ).OpenDatabase(strPath).TableDefs.Count - 1
   For i = 0 To numTables
      'Set our Table Name string
      tabName = LCase(DBEngine.Workspaces( 0 ).OpenDatabase(strPath).TableDefs(i).Name)

      'Check for mssys and ~ prefixed tables and skip them
      If InStr( 1 , tabName, "msys" ) = False And _
            InStr( 1 , tabName, "~" ) = False Then

         'Set the controls focus to print to

         ' lst.SetFocus

         'display the tables using the Object
         'to keep User Case sensitive Names
         lst.AddItem DBEngine.Workspaces( 0 ) _
               .OpenDatabase(strPath).TableDefs(i).Name

      End If
   Next

   Exit Sub

errhandler: 'Capture any Errors

   With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "GetAllExternalTables"
   End With
End Sub

For the above VBA Get all External Access Database Tables Sub in a usable Form with Button, download the Sample database in ZIP format. Place Database in a Directory containing MS Access Databases.

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

For further MS Access tutorials go here: MS Access tutorials

Go here To Check if Table exists in VBA.

Go here To Get all Fields in a Table with VBA

Did you find what you were looking for? What would you suggest? mail@eraserve.com


Your Name:
Your Message:
Your Email:

Custom Search