How to Find an Access Table Inside Multiple ACCDB or MDB Files
Question details
The user needs to locate a specific Microsoft Access table that is scattered among hundreds of MDB or ACCDB files across multiple folders and subfolders.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to locate a specific database table that may have been moved during database splitting or normalization, where standard file-name searches fail.
- Observed behavior
- Standard operating system searches cannot locate the table because the data is stored internally within binary database files rather than existing as standalone files.
Ensure you have read permissions for all directories containing the database files, and verify that system-object visibility is enabled in your Microsoft Access settings.
Use VBA and SQL to Recursively Query Databases
Write a VBA script utilizing the FileSystemObject to loop through directories and query the MSysObjects system table in each database file.
This method automates the process of opening each file and running a SQL query to check if the table exists. It is the most reliable way to accurately identify both local and linked tables.
Launch Microsoft Access, open a blank database, and press ALT + F11 to open the Visual Basic for Applications (VBA) editor.
Click 'Insert' from the top menu and select 'Module' to create a new script window.
Write a script using FileSystemObject to recursively enumerate all files in your target directories. For each '.accdb' or '.mdb' file, apply the SQL query: SELECT MSysObjects.Name FROM MSysObjects IN 'C:\path\filename.accdb' WHERE MSysObjects.Name = 'MyLostTable' AND MSysObjects.Type IN (1, 4, 6);
Press F5 or click the 'Run' button. The script will iterate through the files and return the file paths of any database where the table is found.
Use a Binary Text Search Tool
Utilize specialized third-party grep search tools to scan the raw binary data of the database files for the table name.
Need a Lightweight Alternative for Your Daily Office Tasks?
While WPS Office does not include a database management tool like Microsoft Access, it serves as a powerful, free, and lightweight alternative for your word processing, spreadsheet, and presentation needs. Enjoy seamless compatibility with Microsoft Word, Excel, and PowerPoint without the heavy subscription fees.
- 1. Visit the WPS Website: Go to the official WPS Office website to download the free installation package.
- 2. Install the Software: Run the downloaded installer and follow the on-screen prompts to set up WPS Office on your computer.
- 3. Open Your Documents: Launch WPS Office and easily open, edit, and save your existing Microsoft Office files.
Frequently Asked Questions
Why can't I use standard Windows Search to find the Access table?
Windows Search primarily indexes file names and the contents of standard text documents. Since Microsoft Access tables are embedded within binary .accdb or .mdb files, standard OS search tools cannot read the internal database schema to locate table names.
What does MSysObjects.Type IN (1, 4, 6) mean in my SQL query?
In the hidden MSysObjects system table, 'Type' indicates the kind of object. Type 1 refers to local tables, while Types 4 and 6 refer to linked tables (such as ODBC connections or linked Access tables). Using this filter ensures you only search for tables rather than forms, macros, or queries.
Can I perform this search if the database files are password protected?
If the database files are password protected, the FileSystemObject and SQL automation method will fail unless you explicitly pass the database password in your VBA connection string when attempting to open and query each file.
