Is it possible to identify the last time an object was used? (1 Viewer)

Alc

Registered User.
Local time
Today, 08:30
Joined
Mar 23, 2007
Messages
2,407
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

Well-known member
Local time
Today, 08:30
Joined
Apr 9, 2015
Messages
4,337
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

Registered User.
Local time
Today, 08:30
Joined
Mar 23, 2007
Messages
2,407
Thanks for that. Is it possible to detect the last time a table was referenced?
 

Ranman256

Well-known member
Local time
Today, 08:30
Joined
Apr 9, 2015
Messages
4,337
You can look at the detail proprty info, but I don't think there's a 'last referenced'.
 

sonic8

AWF VIP
Local time
Today, 13:30
Joined
Oct 27, 2015
Messages
998
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

Immoderate Moderator
Staff member
Local time
Today, 07:30
Joined
Feb 28, 2001
Messages
26,996
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

MVP / VIP
Local time
Today, 12:30
Joined
Jan 14, 2017
Messages
18,186
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

Immoderate Moderator
Staff member
Local time
Today, 07:30
Joined
Feb 28, 2001
Messages
26,996
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

You've got your good things, and you've got mine.
Local time
Today, 08:30
Joined
May 21, 2018
Messages
8,463
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
Code:
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.
 
Last edited:

Alc

Registered User.
Local time
Today, 08:30
Joined
Mar 23, 2007
Messages
2,407
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

MVP / VIP
Local time
Today, 12:30
Joined
Jan 14, 2017
Messages
18,186
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

You've got your good things, and you've got mine.
Local time
Today, 08:30
Joined
May 21, 2018
Messages
8,463
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.

Code:
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


Code:
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.

 

Attachments

  • Usage.jpg
    Usage.jpg
    90 KB · Views: 230

isladogs

MVP / VIP
Local time
Today, 12:30
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • SystemUsage.PNG
    SystemUsage.PNG
    63.9 KB · Views: 209
  • CountSystemUsage.PNG
    CountSystemUsage.PNG
    54.9 KB · Views: 224

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:30
Joined
May 21, 2018
Messages
8,463
Code:
 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

MVP / VIP
Local time
Today, 12:30
Joined
Jan 14, 2017
Messages
18,186
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

You've got your good things, and you've got mine.
Local time
Today, 08:30
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom