Listing database objects made easy

VBA Functions to List Access Database Objects

On this page are VBA functions that list the tables, queries, forms and reports in the current database.

DMW uses these functions during the development phases of Access 2003, 2007, 2010, 2013 and 2016 databases.

Each of these functions is intended for use in the Immediate Window of the Visual Basic Editor where it will ouput its list of objects.


VBA Function to List All Tables

This function lists all the tables in the database in which you execute it.

  1. Function dmwListAllTables() As String
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim tbl As AccessObject, db As Object
  4. Dim strMsg As String
  5.  
  6. On Error GoTo Error_Handler
  7.  
  8. Set dB = Application.CurrentData
  9. For Each tbl In db.AllTables
  10. Debug.Print tbl.Name
  11. Next tbl
  12.  
  13. strMsg = "Tables listing complete"
  14.  
  15. Procedure_Done:
  16. dmwListAllTables = strMsg
  17. Exit Function
  18.  
  19. Error_Handler:
  20. strMsg = Err.Number & " " & Err.Description
  21. Resume Procedure_Done
  22.  
  23. End Function

To Execute the VBA Code

To execute the function, copy and paste the code above into a module in your database’s Visual Basic Editor. Then in its Immediate Window type ?dmwListAllTables() and press Enter.

Comments

Note that the list of tables that dmwListAllTables() generates includes the names of system tables that are usually hidden from the database user’s view. Such tables have names beginning with MSys.


VBA Function to Exclude System Tables

This function lists the tables in your database but in so doing excludes from the list the names of database systems tables - those tables beginning 'MSys'.

The difference in code between dmwListAllTablesNotMSys() and dmwListAllTables() is within the For ... Next Loop that starts at line nine.

  1. Function dmwListAllTablesNotMSys() As String
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim tbl As AccessObject, dB As Object
  4. Dim strMsg As String
  5.  
  6. On Error GoTo Error_Handler
  7.  
  8. Set dB = Application.CurrentData
  9. For Each tbl In db.AllTables
  10. If Not Left(tbl.Name, 4) = "MSys" Then
  11. Debug.Print tbl.Name
  12. End If
  13. Next tbl
  14.  
  15. strMsg = "Tables listing complete"
  16.  
  17. Procedure_Done:
  18. dmwListAllTablesNotMSys = strMsg
  19. Exit Function
  20.  
  21. Error_Handler:
  22. strMsg = Err.Number & " " & Err.Description
  23. Resume Procedure_Done
  24.  
  25. End Function

To Execute the VBA Code

To execute the function, copy and paste the code above into a module in your database’s Visual Basic Editor. Then in its Immediate Window type ?dmwListAllTablesNotMSys() and press Enter.


VBA Function to List All Queries

This function lists all the queries in the database in which you execute it.

  1. Function dmwListAllQueries() As String
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim strMsg As String
  4. Dim qry As AccessObject, dB As Object
  5.  
  6. On Error GoTo Error_Handler
  7.  
  8. Set dB = Application.CurrentData
  9. For Each qry In db.AllQueries
  10. Debug.Print qry.Name
  11. Next qry
  12.  
  13. strMsg = "Queries listing complete"
  14.  
  15. Procedure_Done:
  16. dmwListAllQueries = strMsg
  17. Exit Function
  18.  
  19. Error_Handler:
  20. strMsg = Err.Number & " " & Err.Description
  21. Resume Procedure_Done
  22.  
  23. End Function

To Execute the VBA Code

To execute the function, copy and paste the code above into a module in your database’s Visual Basic Editor. Then in its Immediate Window type ?dmwListAllQueries() and press Enter.


VBA Function to List All Forms

This function lists all the forms in the database in which you execute it.

  1. Function dmwListAllForms() As String
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim strMsg As String
  4. Dim frm As AccessObject, dB As Object
  5.  
  6. On Error GoTo Error_Handler
  7.  
  8. Set dB = Application.CurrentProject
  9. For Each frm In db.AllForms
  10. Debug.Print frm.Name
  11. Next frm
  12.  
  13. strMsg = "Forms listing complete"
  14.  
  15. Procedure_Done:
  16. dmwListAllForms = strMsg
  17. Exit Function
  18.  
  19. Error_Handler:
  20. strMsg = Err.Number & " " & Err.Description
  21. Resume Procedure_Done
  22.  
  23. End Function

To Execute the VBA Code

To execute the function, copy and paste the code above into a module in your database’s Visual Basic Editor. Then in its Immediate Window type ?dmwListAllForms() and press Enter.


VBA Function to List All Reports —
dmwListAllReports()

This function lists all the reports in the database in which you execute it.

  1. Function dmwListAllReports() As String
  2. '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  3. Dim strMsg As String
  4. Dim rpt As AccessObject, dB As Object
  5.  
  6. On Error GoTo Error_Handler
  7.  
  8. Set dB = Application.CurrentProject
  9. For Each rpt In dB.AllReports
  10. Debug.Print rpt.Name
  11. Next rpt
  12.  
  13. strMsg = "Reports listing complete"
  14.  
  15. Procedure_Done:
  16. dmwListAllReports = strMsg
  17. Exit Function
  18.  
  19. Error_Handler:
  20. strMsg = Err.Number & " " & Err.Description
  21. Resume Procedure_Done
  22.  
  23. End Function

To Execute the VBA Code

To execute the function, copy and paste the code above into a module in your database’s Visual Basic Editor. Then in its Immediate Window type ?dmwListAllReports() and press Enter.


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.