I have a series of large Access databases (hooked into SQL backend) that are used as primary ERP within an organization.
Thousands of forms and reports built up over 12 years.
I need to do a major tidy up job and find out which form controls and reports are actually being used.
I can easily create a table and stick code on each control on each form to record into the table userID, date, dbname, formname, objectname - but that's obviously a massive job to manually add this code to every single button/dropdown in the databases.
Is there something that can do all this automatically??
What I feel is that you don't need to install code for every control on a Form, but on all Form & Report level. When the User opens a Form or Report he is using that object irrespective of which control he has clicked or updated etc.
I have used this method, i.e. keeping a log with details of usage in a table and removed obsolete Reports after review of the log.
Develop a Function and install it in the Standard Module. If all databases are accessible from a common location (Server) then it is easy to install the function in a separate database and attach it to all the databases as a Library database.
Pass the Form/Report Name as parameter to the Function. Let the function retrieve the object's UserName ( CurrentUser ) and Object Name, Date etc. and record it on the log Table.
You need to install a three or four line code in each Form's and Report's VBA Module to call the main function from there.
Since, you have thousands of Forms/Reports to install the Code in the VBA Module this can be automated and is a one time operation.
You can address each Form and Report by iterating through the Document Object of Forms & Report Container Object.
Set db = Currentdb
Set ctr = db.Containers("Forms")
For Each Doc in the ctr.Documents