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 and Access 2007 databases.

To include any one of these functions in your database, copy and paste it into a module in the Visual Basic Editor of that database, edit out the line numbers shown here in the code listing, and then compile the module.

If you receive an error message, and assuming the rest of your code compiles without error, then most likely the error is due to a missing or faulty reference. So check the references, make any adjustments and recompile.

Each of these functions outputs its list to the Immediate Window in the Visual Basic Editor.

VBA function to list all tables

dmwListAllTables() function

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

VBA function code

  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 run the code, input ?dmwListAllTables() into the Immediate Window 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

dmwListAllTablesNotMSys() function

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.

VBA function code

  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 run the code, input ?dmwListAllTablesNotMSys() into the Immediate Window and press Enter.

VBA function to list all queries

dmwListAllQueries() function

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 run the code, input ?dmwListAllQueries() into the Immediate Window and press Enter.

VBA function to list all forms

dmwListAllForms() function

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 run the code, input ?dmwListAllForms() into the Immediate Window and press Enter.

VBA function to list all reports

dmwListAllReports() function

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 run the code, input ?dmwListAllReports() into the Immediate Window and press Enter.

disclaimer

DMW Consultancy Limited 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.

Copyright © 2004 - 2010 DMW Consultancy Ltd · Tonbridge · Kent