Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-01-2019, 11:55 AM   #1
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,305
Thanks: 48
Thanked 27 Times in 26 Posts
Alc will become famous soon enough Alc will become famous soon enough
Is it possible to identify the last time an object was used?

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?

Alc is offline   Reply With Quote
Old 02-01-2019, 12:01 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,370
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Is it possible to identify the last time an object was used?

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.
Ranman256 is offline   Reply With Quote
Old 02-01-2019, 12:06 PM   #3
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,305
Thanks: 48
Thanked 27 Times in 26 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Is it possible to identify the last time an object was used?

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

Alc is offline   Reply With Quote
Old 02-01-2019, 03:58 PM   #4
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,370
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Is it possible to identify the last time an object was used?

You can look at the detail proprty info, but I don't think there's a 'last referenced'.
Ranman256 is offline   Reply With Quote
Old 02-02-2019, 03:15 AM   #5
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 269
Thanks: 44
Thanked 76 Times in 73 Posts
sonic8 is on a distinguished road
Re: Is it possible to identify the last time an object was used?

Quote:
Originally Posted by Ranman256 View Post
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...
__________________
New article:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 02-02-2019, 06:16 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,571
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-02-2019, 06:29 AM   #7
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 02-02-2019, 06:46 AM   #8
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,571
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 02-02-2019, 06:59 AM   #9
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,848
Thanks: 36
Thanked 563 Times in 532 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Is it possible to identify the last time an object was used?

Quote:
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 by MajP; 02-02-2019 at 07:09 AM.
MajP is offline   Reply With Quote
Old 02-04-2019, 04:13 AM   #10
Alc
Newly Registered User
 
Join Date: Mar 2007
Location: Ontario
Posts: 2,305
Thanks: 48
Thanked 27 Times in 26 Posts
Alc will become famous soon enough Alc will become famous soon enough
Re: Is it possible to identify the last time an object was used?

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.
Alc is offline   Reply With Quote
Old 02-04-2019, 04:25 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

Quote:
Originally Posted by Alc View Post
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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 02-04-2019, 05:03 AM   #12
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,848
Thanks: 36
Thanked 563 Times in 532 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Is it possible to identify the last time an object was used?

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.

Attached Images
File Type: jpg Usage.jpg (90.0 KB, 75 views)
MajP is offline   Reply With Quote
Old 02-04-2019, 06:26 AM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

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
Attached Images
File Type: png SystemUsage.PNG (63.9 KB, 61 views)
File Type: png CountSystemUsage.PNG (54.9 KB, 63 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 02-04-2019, 06:53 AM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,848
Thanks: 36
Thanked 563 Times in 532 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Is it possible to identify the last time an object was used?

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?
MajP is offline   Reply With Quote
Old 02-04-2019, 07:21 AM   #15
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,011
Thanks: 114
Thanked 3,014 Times in 2,741 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Is it possible to identify the last time an object was used?

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...d.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.

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Identify time slots used to perform a piece of work yetirider General 3 09-22-2016 11:42 AM
Query to identify if a time period is between times that pass through midnight? keeper Queries 26 06-05-2016 04:22 PM
I want to identify the data in an interval of time between two days leeamra Queries 4 03-06-2016 12:17 PM
identify cycling/fluctuations in a time series data sany1234 General 2 03-13-2013 04:48 AM
Combo to Identify Time Value maya Forms 1 09-08-2003 09:39 PM




All times are GMT -8. The time now is 05:06 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World