How to Use VBA to Check an Access Database File Extension
Question details
The user needs to write a VBA script to determine the file extension of the currently open Access database (such as ACCDR or ACCDE) and trigger specific actions based on the detected extension.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Verifying the database file type automatically upon startup to enforce security measures, distinct user workflows, or runtime environments.
- Observed behavior
- The correct VBA syntax and event placement (such as an AutoExec macro or a Form On Open event) are required to automatically detect the file extension and execute commands like closing the application.
Ensure you have macro security enabled in your Access database settings and that you have a basic understanding of VBA modules and database startup events.
Inspect the Extension via Startup Form On Open Event
Extract the file extension using VBA's CurrentDb.Name function, triggered automatically when the main database form loads.
By attaching the VBA code to the On Open event of a designated startup form, the database can immediately evaluate if it is running as an ACCDR or ACCDE file and execute commands, such as Application.Quit, if an unauthorized format is used.
Open your database in Access, right-click your startup form in the Navigation Pane, and select 'Design View'.
Press F4 to open the Property Sheet, navigate to the 'Event' tab, and click the ellipsis (...) next to the 'On Open' property.
Choose 'Code Builder' from the prompt to open the VBA editor interface.
Insert your extension checking logic into the subroutine. For example: If LCase(Right(CurrentDb.Name, 5)) = "accde" Then Application.Quit
Save the VBA module, close the editor, and switch the form to 'Form View' to test the event trigger.
Create a Public Function Called by an AutoExec Macro
Define a standalone VBA function to check the extension and utilize an AutoExec macro to run it before any forms are loaded.
Looking for a Free Office Suite? Try WPS Office
While WPS Office does not natively support Microsoft Access databases, it serves as a lightweight, powerful, and free alternative for all your document, spreadsheet, and presentation needs. For advanced automation, WPS Spreadsheet offers robust VBA and macro support compatible with Excel formats.
- 1. Visit the WPS Office Website: Navigate to the official WPS Office website to find the free installer package.
- 2. Download the Installer: Click the download button to get the most recent version of WPS Office for your operating system.
- 3. Install and Set Up: Run the setup file and follow the on-screen instructions to install the suite and enjoy high compatibility with major Office formats.
Frequently Asked Questions
What is the difference between an ACCDE and an ACCDR file in Microsoft Access?
An ACCDE file is a locked-down version of an Access database where all VBA code is compiled and cannot be modified. An ACCDR file is a standard database that has been renamed to force it to open in runtime mode, mimicking the environment of a user who does not have the full Access application installed.
Why does CurrentDb.Name return the full file path instead of just the file name?
The CurrentDb.Name property evaluates to the absolute directory path of the database file (e.g., C:\Folder\Database.accdb). To extract just the extension, you must utilize string manipulation functions like Right() or InStrRev() to isolate the characters located after the final period.
How do I bypass the AutoExec macro if I need to edit the database design or VBA code?
You can bypass all startup options, including the AutoExec macro and startup forms, by holding down the Shift key while opening the database file in Microsoft Access. This allows developers unhindered access to edit the database objects and VBA modules.
