Solved

Query to show when an object was last updated/modified

Posted on 2016-08-24
17
29 Views
Last Modified: 2016-08-24
I am trying to show in my query when an object (form, query, report, table, etc) was last modified. I am using this query but the "DateUpdate" field doesn't seem accurate. There are forms that I modified yesterday but still show the date as 2014 when it was last modified. Is there another place where I can pull this info instead of using this query source? It doesn't seem to be accurate and I don't get why either.

SELECT MSysObjects.Type, MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.DateCreate, MSysObjects.Connect, MSysObjects.Database, MSysObjects.Owner
FROM MSysObjects
WHERE (((MSysObjects.Type)<>2 And (MSysObjects.Type)<>3 And (MSysObjects.Type)<>-32757) AND ((Left([Name],1))<>'~') AND ((Left([Name],4))<>'Msys'))
ORDER BY MSysObjects.DateUpdate DESC , MSysObjects.DateCreate DESC;

Open in new window

0
Comment
Question by:Lawrence Salvucci
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41768659
It is correct, that form updates are not recorded this way while, say, queries are.

Many have observed this behaviour through the years, but as far as I know, there is no solution.
You'll have to find other methods.

/gustav
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768663
Do you know another method to get the actual modified date for any objects? I don't know of any other hence why I posted this question.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41768677
No. You can list all the properties of an object but for forms, a LastChangedDate is not among them.

/gustav
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41768686
<<No. You can list all the properties of an object but for forms, a LastChangedDate is not among them.>>

 It's part of the Document Properties, but still, the last modified date cannot be relied on as gustav said.

It's been working on and off for a number of years and as a result, no one ever relies on it.

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768691
And MS just doesn't care to fix it I take it? I wish there was another way to get that data.
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41768706
FYI:

Sub DocumentModified()
      Dim dbs As Database, ctr As Container, doc As Document

      ' Return reference to current database.
      Set dbs = CurrentDb
      ' Return referenct to Forms container.
      Set ctr = dbs.Containers!Forms
      ' Enumerate through Documents collection of Forms container.
      For Each doc In ctr.Documents
            ' Print Document object name and value of LastUpdated property.
            Debug.Print doc.Name; "      "; doc.LastUpdated
      Next doc
      Set dbs = Nothing

End Sub
0
 
LVL 57
ID: 41768712
<<And MS just doesn't care to fix it I take it? I wish there was another way to get that data.>>

 That is more or less correct.   They've taken several stabs at it, but so few use the field, when it breaks it's not a high priority.

 Typically where it gets used is in conjunction with source control, and due to the name of Access, it's rare that more than one developer works on an app at one time.

 So source control is another thing with Access that they tried a few times with and then gave up on it.

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768714
Thank you for that, Jim. Unfortunately I can't use that since it's not truly accurate.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768718
Lovely. I wanted to use it so I knew which objects I've updated recently in my beta DB so I can easily just import those into my live DB instead of having to go through all the objects to see which ones I've updated, etc. I have 1307 objects in my DB so I was hoping to use that date field. Oh well. I'll have to find another way to do this.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41768737
If you are the only developer, you don't need to merge objects.  As long as the application is properly split, you can make changes to your copy of the FE and when they are ready to be distributed, you replace the production copy of the FE.  Then your distribution procedure will pull the updated version the next time a user clicks on his shortcut.

I use a .bat file for distribution.  The users have a shortcut that points to a batch file on the server.  The batch file copies the production copy of the FE to the user's C: drive and then opens it.  This way, every time the user opens the FE, he gets a fresh copy.  There are more sophisticated methods that only get new copies when a change was made but I find that the FE size is small enough that just giving them a new copy each time doesn't slow down the app and it means they will never have to compact the FE.

You could also look into the tool for comparing databases offered at the FMSINC website.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768750
I like the idea of using the .bat file. Could you maybe share how you have that set up? The problem I have with replacing the FE is that the beta copy that I use on my laptop to build and update objects isn't linked to the live BE. I use a BE beta copy on my laptop that is an exact copy of the live BE. It just runs faster if I have the beta copies of both the FE & BE on my laptop. So every time I replace the live FE I need to relink the tables to the live BE. And then I distribute the new FE to all the users. We use a Citrix environment here so all the FE's are actually stored on the citrix server instead of the users actual desktops & laptops. It's just easier for deployment because then I can just replace all the user FE's without having to wait for users to log in and start up their computers, etc. We don't have any performance issues with the FE's being on the citrix server either.

I would love some input on how to streamline this process from using my beta FE & BE to replacing the live FE and relinking the tables to the live BE.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41768777
Seems like my old article was meant for you:

Deploy and update a Microsoft Access application in a Citrix environment

/gustav
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768781
HA! I will read that thoroughly! Thank you for that!
0
 
LVL 57
ID: 41768794
<<I would love some input on how to streamline this process from using my beta FE & BE to replacing the live FE and relinking the tables to the live BE.>>

The other way to do that is to use a "Launcher" app to execute the DB.  Something along the lines of:

http://autofeupdater.com/

  Or you can do your own as gustav has shown.

 As far as relinking, the way to do that is at app startup, check a link, and if broken, prompt the user for the location of the DB.   There are lots of relinking code out there that does this.   One such can be found here:

http://www.jstreettech.com/downloads.aspx

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768838
Let me digest everything and I will post back. This question has taken a different turn so not sure if I should post this as a new question or not. I will post back shortly. Thank you for all your input!
0
 
LVL 57
ID: 41768861
<< so not sure if I should post this as a new question or not.>>

 That would be best.  This one should be closed off and I should have said that in my last comment rather than posting what I did.

Jim.
0
 
LVL 1

Author Comment

by:Lawrence Salvucci
ID: 41768864
I will close this one out and open another one in a little bit. Thanks Jim.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now