VBA & Macro Problems

How to Create Folders and Subfolders from Access Table Records Using VBA

Natalie Taylor Oct 16, 2026 869 views

Question details

The user needs to create a parent folder and multiple subfolders programmatically based on data stored in Microsoft Access tables.

How to Create Folders and Subfolders from Access Table Records Using VBA

Product: Microsoft Access
Device & OS: not provided
Scenario: Automating directory creation from a database query utilizing VBA scripting.
Observed behavior: The user successfully creates a single folder using FileSystemObject but struggles to correctly configure the recordset loop to generate all required subfolders dynamically.

Before you start

Ensure that your table query successfully returns the concatenated folder paths and verify that the 'Microsoft Scripting Runtime' reference is enabled in your VBA Editor.

Solution 1Recommended

Use DAO Recordset and FileSystemObject to Loop and Create Folders

The most reliable method to create directories from table data is to iterate sequentially through a DAO recordset while using the FileSystemObject to check and create missing paths.

Using DAO allows you to read sequentially through the records returned by your query. Combining this with the FileSystemObject makes it easy to verify if a folder path already exists before attempting to create it, effectively preventing runtime errors during execution.

Enable Required References

Open the VBA Editor by pressing Alt + F11. Navigate to Tools > References and check the boxes for 'Microsoft Scripting Runtime' and 'Microsoft Office Access database engine Object Library'.

Declare Object Variables

In your module, declare variables for the DAO Recordset (e.g., Dim rs As DAO.Recordset) and the FileSystemObject (e.g., Dim fso As Object).

Initialize Objects and Open Recordset

Set the FileSystemObject using 'Set fso = CreateObject("Scripting.FileSystemObject")' and open your query with 'Set rs = CurrentDb.OpenRecordset("qryConcatenatedPath")'.

Construct the Recordset Loop

Create a 'Do While Not rs.EOF' loop. Inside the loop, extract the folder path field from the current record.

Check and Create Folders

Within the loop, use an 'If Not fso.FolderExists(path)' condition. If the condition is met, call 'fso.CreateFolder(path)' to build the missing parent or subfolder.

Move to Next Record and Clean Up

Place 'rs.MoveNext' at the end of your loop. Once the loop finishes, close the recordset ('rs.Close') and set both 'rs' and 'fso' to Nothing.

Use DAO Recordset and FileSystemObject to Loop and Create Folders

Preventing Runtime Errors: Always verify if the target folder exists before calling the CreateFolder method to prevent triggering runtime error 58 ('File already exists').

Free Microsoft Office alternative

Explore WPS Office for Your Data and Documentation Needs

While Microsoft Access is required for specific database programming tasks, WPS Office provides a robust, free alternative for your daily spreadsheet, document, and presentation needs with exceptional Microsoft Office compatibility.

1. Download the Installer: Visit the official WPS Office website and click the free download button.
2. Install the Software: Run the downloaded executable file and follow the straightforward on-screen instructions.
3. Open Your Office Files: Launch WPS Office and immediately start working seamlessly with your existing Microsoft Office files.

Free, lightweight, and fast installation.Highly compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Built-in PDF toolkit for easy editing and conversion.Familiar user interface requiring no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'User-defined type not defined' error when using FileSystemObject?

This error occurs because the Microsoft Scripting Runtime library is not referenced in your project. In the VBA Editor, go to Tools > References and check the box next to 'Microsoft Scripting Runtime'.

How do I reference the specific folder path field from my Access table?

Inside your DAO loop, you can reference the field containing the path using 'rs!FieldName' or 'rs.Fields("FieldName").Value', replacing 'FieldName' with the actual column name from your database query.

Can FileSystemObject create multiple levels of nested subfolders at once?

No, the FileSystemObject CreateFolder method can only build one directory level at a time. To create nested subfolders, your code must create the parent folder first, then create the child folder inside it sequentially.

Excel & Data Problems