Decomposing your Access application into external files can be useful for external process automation of source code, version control, or disaster recovery.
An Access application is composed of objects of different nature, like forms, reports, queries, macros, code and class modules.
Since Microsoft Access serves two fundamentally different roles, it makes sense that these two roles should maintain a healthy separation. One of the first lessons every Access developer should learn is the importance of splitting their application into front-end and back-end files.
We’re talking about the front-end role here.
However, that being done, exporting the different database components of the Access application only as text is not practical, or even possible, as components, like reports, do not support it, and they cannot be directly imported from text files.
That’s rather inconvenient if you want to use external tools, like version control systems.
Decomposing the front-end
You can download this VBA code module, which has one public Sub (ExportVBProject) that will export the components of your Access database and project into text files, for Access objects that support that. For other components, like form, reports, queries and macros, it will export them into another database.
The process is automated by taking into account the version of your application as defined in a global constant (APP_VERSION), and the current date.
It’s like a poor man’s manual version control system.
But It can also come to the rescue when a component, or the entire database, becomes irreversibly corrupted, or you want to grab a previous version of a component, like a form.
(Tables are out of scope here).
How to use
First, you have to import the module in your VBA Project (or copy/paste the code wherever you want to).
Add a reference to the “Microsoft Visual Basic for Applications Extensibility 5.3” ActiveX library.
Then go to the VBA debug window ([ctrl]+[g]), type “ExportVBProject” (without the quotes) and press [enter].
As there can be more that one VB(E) project present in the VBA environment, the procedure will ask you to choose the one you want to export.
It will then ask you if you want to run it in test mode, or not. In test mode, nothing get exported, it’s just dumping what it would do.
How it works
An export subdirectory is built from the current VBA project path, named “sources”. Under that, another subdirectory is created, from a global string constant named “APP_VERSION”. This is meant to be the current version of your app (like “01.00.00”). You have to manually update that to your current version, if you keep it that way, or you can customise this behavior as it fits you.
sExportDir = CombinePath( _
"sources\" & APP_VERSION & _
"\" & Year(Now) & _
Format$(Month(Now), "00") & _
Format$(Day(Now), "00") _
Then, objects that support a form of export as text are exported as corresponding text files. The others, like form, reports, macros, queries (and documents) are exported into a new Access database, built on the fly.
Last, but not least, the VBA project settings are also exported in a text file, including conditional compilation arguments and COM/ActiveX references details.
Hopefully, this will give a head start to craft or integrate your own tools.
I’m on twitter, @francescofoti