MS Access Databases – Properties: How To Set It Using Access VBA And The DAO Object
As with most Microsoft Office applications, MS Access databases also have a properties feature that captures the basic file attributes like author, date and subject to name a few. There is also a custom tab to edit and add your own properties and is stored with the database file itself.
To know more about changing the built-in properties window, see view and change MS Office 2010 file properties. For the earlier versions (pre 2007), this feature can be found via the File menu.
In this article, I want to introduce some Access VBA code to manage and control custom properties instead which is not part of the above collection but instead is permanently stored within the database file using DAO (Database Access Object).
MS Access Databases – Properties: How To Set It Using Access VBA With And DAO Object
This approach provides a hidden layer away from general users and it also allows for more flexibility when wanting to say share properties across other databases.
Once set, it remains set until of course you use VBA code again to delete or modify values which provide a distinct advantage over the method of setting global constant variables in a module (which by the way is not updatable and only local to the database it resides during run-time).
Here’s how to create and call such property…
Open your database file and jump to a new module object either from the Create tab on the Ribbon bar or via the Visual Basic Editor Window, inserting a new module there.
In order to use the DAO object, you will need to first load its library as it will release the additional members required and extend MS Access databases functionality. In the VBE (Visual Basic Editor), go to the Tools menu and choose References…
As the illustration above shows, locate and choose ‘Microsoft DAO 3.6 Object Library’.
Earlier versions may have a different reference number.
In my example, I want to add my copyright notice stamp and show the current year too so it will look like:
(C) Access Database Tutorial 2013
In the new blank module add the following code:
Save your new module and run the procedure called ‘Sub RunCreateProperty()‘ by placing the cursor in the code line for this procedure and press the F5 function from the keyboard.
Nothing will appear to have happened and if written correctly (as above) and should you press the F5 function key again, it will give you an error this time which means the property already exists (so it was successfully executed the first time around).
Of course, if you do the same to the procedure called ‘Sub RunDeleteProperty()‘ then it will remove and destroy the property (should you wish to).
To use this new added custom property, you can write code in a new procedure, create a function for re-use in modules, and call it into queries, forms and reports too. The simple piece of VBA code here would look something like:
Finally, you can modify to open the scope of the above and use it to call this across other MS Access databases adding arguments (parameters) and other members of the DAO object including the method ‘OpenDatabase‘.
Want to know how to program Microsoft Access? I can teach you here online now at your own pace tailored to your exact requirements. You just pay by the hour!