VBA & Macro Problems

How to Loop Through All Database Objects in Access VBA

Khadija Khan Oct 16, 2026 870 views

Question details

The user needs to loop through and process database objects (tables, queries, forms, macros, reports) in an external Access database using an Access VBA procedure.

How to Loop Through All Database Objects Using Access VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating the processing of objects in an external database via a VBA script.
Observed behavior: Using Dbs.AllQueries throws an error because AllQueries is a property of the Access.CurrentData object model, not the DAO.Database object model.

Before you start

Ensure that the Microsoft DAO Object Library reference is enabled in your VBA editor (Tools > References) so your script can interact with external database objects correctly.

Solution 1Recommended

Use DAO Collections to Loop Through External Database Objects

Replace CurrentData collections with native DAO Database collections such as QueryDefs and TableDefs to correctly iterate through objects in an external database.

When you connect to an external database and instantiate a DAO.Database object (often declared as 'Dbs'), you cannot use Access UI-specific objects like 'CurrentData.AllQueries'. These properties only apply to the currently open database in the application. Instead, you must use the native Data Access Objects (DAO) engine collections.

Declare the appropriate DAO objects

Open your VBA editor and locate your external database connection script. Declare a DAO QueryDef object by typing 'Dim Qdf As DAO.QueryDef'.

Replace the AllQueries loop

Remove your 'For Each... in Dbs.AllQueries' statement. Replace it with the correct DAO loop: 'For Each Qdf In Dbs.QueryDefs'.

Process the object properties

Inside your loop, you can now safely process each query by referencing its name using 'Qdf.Name'.

Repeat for tables

To loop through tables, declare 'Dim Tdf as DAO.TableDef' and use the loop 'For Each Tdf In Dbs.TableDefs'.

Use DAO Collections to Loop Through External Database Objects

Access Specific Objects (Forms, Reports, Macros): Forms, reports, and macros are Microsoft Access application objects rather than native DAO engine objects. To loop through them in an external database, you must typically launch a new Access.Application instance via VBA and open the external file.

Free Microsoft Office alternative

Looking for a Lightweight Office Alternative for Data Management?

While Microsoft Access handles complex relational databases, many automated data tracking, reporting, and VBA tasks can be efficiently managed using WPS Office. As a lightweight and free alternative, WPS Spreadsheets supports VBA macros and provides seamless compatibility with Microsoft Excel formats.

1. Download WPS Office: Visit the official WPS website and download the free WPS Office installer.
2. Install and Launch: Run the quick setup and open WPS Spreadsheets to start managing your data.
3. Enable VBA Functionality: Ensure the VBA module is installed within WPS Office to seamlessly run and edit your macro scripts.

High compatibility with Microsoft Office formats (.xlsx, .xls, .docx, .pptx)Robust VBA macro support for automating spreadsheet and data processing tasksLightweight installation with a familiar, user-friendly interfaceCompletely free to download with built-in PDF tools

Frequently Asked Questions

Why do I get an error when using Dbs.AllQueries in my VBA code?

The 'AllQueries' property belongs to the 'Access.CurrentData' object, which only works for the database currently open in the Access user interface. When referencing an external database via a DAO.Database object ('Dbs'), you must use the 'DAO.QueryDefs' collection instead.

How can I loop through tables in an external database using DAO?

You can iterate through the tables by using the 'TableDefs' collection of your DAO Database object. Set up a loop using 'For Each Tdf In Dbs.TableDefs' after declaring 'Tdf' as a DAO.TableDef.

Can I use DAO to loop through forms and reports in an external database?

No, forms, reports, and macros are Access application-level objects, not native DAO database objects. To process them in an external database, you must open that database using a separate 'Access.Application' object instance in your VBA code.

Excel & Data Problems