Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2011

    Unanswered: User usage tracking - help


    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??

    Many thanks to anyone who can help.

  2. #2
    Join Date
    Jan 2009
    Kerala, India
    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.

    1. 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.
    2. 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.
    3. 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.
    4. 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
         Docmd.OpenForm Doc.Name,acDesign
    Now, for example of writing the VBA Code into the Form Module you can refer the Article: Creating Animated Command Button with VBA
    Last edited by apr pillai; 10-10-11 at 11:54. (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts