Is it possible to identify the last time an object was used?

[Alc]

We have a number of databases that contain many, many queries and I'd like to delete those that nobody has used for a while.

I've eliminated from consideration all those that are a source for some other object but I'm still left with a lot.

I can identify various properties of Access objects using VBA. What I'm wondering is whether the last time an object was actually opened (not Date Created and not Date Modified) is stored anywhere within the database?

 

[Ranman256]

I would ignore this. The possibility of deleting a needed query is too much to risk.
They don't take up room, the tables do.
Keep an eye on the tables.

 

[Alc]

Thanks for that. Is it possible to detect the last time a table was referenced?

 

[Ranman256]

You can look at the detail proprty info, but I don't think there's a 'last referenced'.

 

[sonic8]

I would ignore this. The possibility of deleting a needed query is too much to risk.

Keeping obsolete objects carries not just risk but a guarantee of increased complexity, maintenance and overhead.
Get rid of the cruft, now!


There are tools, the most simple being the built-in "Object Dependencies", that help you assess the relevance of existing objects and analyze your database design. - I guess you use those already.


If that does not help, there is not much left than brute force. - Make a backup copy of the file, delete the potential obsolete stuff and wait for any user to complain. - That might take some time...

 

[The_Doc_Man]

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first.

Translation: If you need to know something, you have to store that something. Access does not have a date/time of last object use so if you want one you will have to "roll your own" and remember it.

In order to do this, you will need to control access to that table by NEVER showing the navigation aids and instead hiding everything via a dispatcher/switchboard form. Then when you want to touch a table, you have to launch a form of some kind. Forms have the ability to make note of the time & date you wanted to remember.

 

[isladogs]

Agree with sonic8.
Unwanted queries don't take up much space but do add clutter.
Unwanted tables also waste space and may slow your database.

Another approach you can use is to is to rename objects you think can be deleted e.g. by prefixing with a ~ then setting as a hidden object so they do not appear in the navigation pane.
If nothing seems broken after a couple of weeks or so, delete them.

 

[The_Doc_Man]

Concur with Sonic8 about the "Object Dependencies" feature of Access. Don't forget it has TWO views: Objects depending on... and Objects depended on... (i.e. BOTH directions of dependencies). However, if you have ANY CASE where you dynamically create SQL then the things that dynamic string uses will NOT show up. You would have to manually review any such dynamic dependencies.

 

[MajP]

If that does not help, there is not much left than brute force. - Make a backup copy of the file, delete the potential obsolete stuff and wait for any user to complain. - That might take some time...

Maybe you could do this in reverse and add features to start logging usage. Then determine what is getting used and what is not. So instead of adding back in you could slowly remove things that do not appear to be getting used.

For forms and reports you could do something like
http://allenbrowne.com/AppLogDocUse.html

You could add some time stamping to tables

I am thinking maybe you run a timer on a hidden form and do something like this and write to a log. Once you do your cleanup you would remove this tracker because I assume you will get a performance hit doing this logging. I demoed this and seemed to work

Private Sub Form_Timer()
  
  Dim intState As Integer
  Dim intCurrentType As Integer
  Dim strCurrentName As String
  Dim strType As String
  'Set timer at a high value. For test only did 30 seconds. But likely you want minutes
  Const TimerSeconds = 30
  Me.TimerInterval = 1000 * TimerSeconds
  
  intCurrentType = Application.CurrentObjectType
  strCurrentName = Application.CurrentObjectName
  Select Case intCurrentType
    Case acForm
      strType = "Form"
    Case acReport
      strType = "Report"
    Case acTable
      strType = "Table"
    Case acModule
      strType = "Module"
    Case Else
      strType = "Other there are several more "
  End Select
    'Log it here
    Debug.Print "Log That " & strType & " " & strCurrentName & " " & Now()
  End Sub

I would log to an external database so that you do not blow up the current db and can pull that data off periodically.
Maybe to limit the size of the data instead of logging occurrences you update the count of times open and total recorded time open.

To determine what is not used you can do a query against the system table to find what objects are not recorded. Or if only doing counts you could use the system table to get the name of all objects and write to that table total openings.

 

[Alc]

Thanks all for the tips.

I've already got various tracking running on anything I built since coming here, it's the old databases that preceded my arrival where the (possible) problems exist.

I found some code to check if a particular query is used as a data source by any objects and I can search the VBA for any references to it. I think it's going to be the slow route of renaming what might be useless, leaving it for a while, then deleting it if nothing bad happens.

 

[isladogs]

I found some code to check if a particular query is used as a data source by any objects and I can search the VBA for any references to it. I think it's going to be the slow route of renaming what might be useless, leaving it for a while, then deleting it if nothing bad happens.

Have a look at the free VTools addin which includes a deep search of all VBA code

 

[MajP]

I demoed a usage tracker with the hidden form at startup. I set the timer at a short interval to test, but you could set it like every couple of minutes. After creating a table from msysobjects. Seemed easy and worked pretty well. You could get more exact by putting code in every form and report, but that would be a lot of work.

Private Sub Form_Timer()
  Dim intState As Integer
  Dim intCurrentType As Integer
  Dim strCurrentName As String
  Dim strType As String
  
  intCurrentType = Application.CurrentObjectType
  strCurrentName = Application.CurrentObjectName
  If lastObject <> strCurrentName Then
     lastObject = strCurrentName
     LogUse strCurrentName
  End If
End Sub

Public Sub LogUse(ObjectName As String)
  Dim strSql As String
  TimesUsed = Nz(DLookup("TimesUsed", "tblUsageLog", "Name = '" & ObjectName & "'"), 0)
  strSql = "Update tblUsageLog Set TimesUsed = " & TimesUsed + 1 & ", LastUsed = #" & Format(Now(), "mm/dd/yyyy hh:mm:ss ampm")
  strSql = strSql & "# WHERE Name = '" & ObjectName & "'"
  CurrentDb.Execute strSql
End Sub

This could be easily be expanded to track things used indirectly. If a form is active you could read all objects with rowsource or recordsource and determine which tables and queries they use and log those as well.

 

[isladogs]

I did exactly what MajP suggested in my largest COMMERCIAL database used in UK schools. Every event was tracked over several years

The data could be interrogated by user or by item:

Although extremely useful (e.g. for prioritising for development work), I can confirm it was a HUGE amount of work to do

However, it also enabled me to setup an automatic error logging system whereby details of any program errors (who/what/where/when) were automatically (and silently) sent to me by email. Doing this meant I was able to precisely identify all such errors and over a short period of time fix all of them. Clients were of course made aware of this before it wa implemented.

My measure of success was that the automatic error emails completely stopped after a month or so

 

[MajP]

 I can confirm it was a HUGE amount of work to do

I assume by that you did it the efficient way by putting code in the forms and report and other code, not the inefficient way of using a timer.

Could you use extensibility to write the code to write the code?

 

[isladogs]

I did it the efficient way over a period of several months. Unfortunately by the time I did so, the database was already a behemoth with an FE of around 140MB. See the statistics in post #2 of this thread https://www.access-programmers.co.uk/forums/showthread.php?t=296860

I use VBE extensibility for lots of things though, from memory, I don't think it would help for this purpose. I have to admit, I'd probably never used that library when I set up the system almost 10 years ago.

I've never really used data macros which were introduced in A2010 after this was done. Not sure whether these would do the same job but with less work.

Another thing the logging was useful for was closing down the app after a specified period of inactivity. If the user was still on the same form control or similar after say 20 minutes, they got a warning then the app closed.

 

[MajP]

so it seems that there are some viable solutions for tracking the usage from the very simple and not very efficient to very exact and more efficient.