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!
Tags: access vba, ms access database properties, ms access databases, ms access module, vba programming code
How do you modify the above in order to include the OpenDatabase method?
There are various ways to load your procedure when a database opens.
Consider attaching it a start-up form using the ‘Open’ event or have a general AutoExec macro to run your function when the database loads.
In DAO, there are wasy to create custom VBA code to pass arguments across but this would need some planning before I could give you a real solution – what did you have in mind?
Thanks Ben, I was referring to the final part of your article, “adding arguments (parameters) and other members of the DAO object including the method ‘OpenDatabase‘.” How do you do that?
When you run the RunCreateProperty Sub, the property gets created temporarily or does it survive once you close the database and reopen it?
Hi Giorgio, When you run the RunCreateProperty procedure, it is remembered to that database as it is added to the Database Properties collection. You will need to run the DeleteProperty procedure to remove and reset this member.
The DAO challenge simply refers to your own custom procedures as there is no pre-determined set of agruments – it was mentioned for the more advanced VBA programmer who would have knowledge of how to create their own additional library of members perhaps via global modules or even to the next stage of using class modules.
I don’t have any specific examples at hand – would need to think about this and maybe I could create a part post on this thread in the near future!