Last updated object in a database (1 Viewer)

[deletedT]

It should be simple, but apparently I'm missing something here.

I'm trying to find the last updated object in a database.

    Dim DT As Date
    Dim Obj As String
    
    DT = DMax("DateUpdate", "MSysObjects")
    Debug.Print DT
    
    Obj = Nz(DLookup("Name", "MSysObjects", "DateUpdate=#" & DT & "#"))
    Debug.Print Obj

The print result for DT is correct.
But obj is returning a null value.

What am I doing wrong here?


thanks.

 

[arnelgp]

With CurrentDb.OpenRecordset _
    ("select top 1 [name], [dateupdate] from msysobjects order by [dateupdate] desc;")
    .MoveFirst
    While Not .EOF
        Debug.Print !Name, !dateupdate
        .MoveNext
    Wend
End With

 

[June7]

Weird but this worked for me:

DLookup("Name", "MSysObjects", "CStr(DateUpdate)='" & DT & "'")

 

[isladogs]

Variation on a theme …
Create a query qryMSysObjects

SELECT TOP 1 MSysObjects.Name, MSysObjects.DateUpdate
FROM MSysObjects
ORDER BY MSysObjects.DateUpdate DESC;

Then
Debug.Print DLookup("name", "qryMSysObjects")

 

[deletedT]

Variation on a theme …
Create a query qryMSysObjects

SELECT TOP 1 MSysObjects.Name, MSysObjects.DateUpdate
FROM MSysObjects
ORDER BY MSysObjects.DateUpdate DESC;

Then
Debug.Print DLookup("name", "qryMSysObjects")

I was wondering why DLookup doesn't work. I even created a query as following. DLookup failed to pull the info I needed even from this query.

SELECT MSysObjects.Name, MSysObjects.DateUpdate
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type) In (-32768,1)));

 

[deletedT]

Weird but this worked for me:

DLookup("Name", "MSysObjects", "CStr(DateUpdate)='" & DT & "'")

I tried a lot of combination of using "[]" for field name but never this one.

Thanks.

 

[deletedT]

I marked the post as solved, because with the advices I received I'm able to do what I intended to do.

But Still have no idea why Dlookup doesn't work in my main post above.
In case anyone finds an answer to this puzzle in feature, I appreciate if you share your knowledge.

Thanks again for all your help.

 

[isladogs]

I marked the post as solved, because with the advices I received I'm able to do what I intended to do.

But Still have no idea why Dlookup doesn't work in my main post above.
In case anyone finds an answer to this puzzle in feature, I appreciate if you share your knowledge.

Thanks again for all your help.

I thought you would want a reason as well as a solution!

There was nothing wrong with your original code. If you try something like that on a standard table it will work

In fact similar code will also work in another system table which is editable

 Dim N As Long
    Dim Obj As String
    
    N = DMax("ID", "MSysNavPaneObjectIDs")
    Debug.Print N
    
    Obj = Nz(DLookup("Name", "MSysNavPaneObjectIDs", "ID=" & N))
    Debug.Print Obj

However MSysObjects is a read only system table. I believe that running the DLookup using that expression requires Access to run a process against the table ...which, being read only, it can't do.

If you want to investigate further, you could easily test by making a copy of MSysObjects then removing its read only attribute.
I don't have time to do any more checks as its my daughter's wedding tomorrow and I still have to write my speech....
Wish me luck

 

[June7]

Whatever that 'process' is, DLookup works if the date field and parameter are converted to strings. See post 3.

 

[isladogs]

Whatever that 'process' is, DLookup works if the date field and parameter are converted to strings. See post 3.

OK .. speech written...

Yes I saw that worked.
Sometimes its possible to 'trick' Access and overcome built in restrictions.

For example, in some cases I can delete selected records from MSysObjects even though its read only. See https://www.access-programmers.co.uk/forums/showthread.php?t=293579

Similarly, its also possible to view the contents of deep hidden tables such as those used in column history feature for memo fields ...if you know how to bypass Access restrictions. See http://www.mendipdatasystems.co.uk/column-history-memo-fields/4594523656

EDIT:
Forgot to say earlier but Tera's query from post #5 worked for me though the filter means it is limited to local tables and forms.
This also works for any object other than those system tables starting with MSys - not all system tables have that as a prefix

SELECT TOP 1 MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*"))
ORDER BY MSysObjects.DateUpdate DESC;

Of course, if the WHERE clause is removed, its the same query as I used in post #4

 

[deletedT]

First of all congratulation for your daughter's wedding and I wish her and her partner a lifetime of love and happiness.

I know you're busy due to the ceremony and I shouldn't post this reply now, but for further references:

However MSysObjects is a read only system table. I believe that running the DLookup using that expression requires Access to run a process against the table ...which, being read only, it can't do.

I'm not sure about anything here, but I don't think it's the problem here. Because I believe if vb engine is programmed to not to respond to DLookup on read only objects or DLookup is designed to return null values for these tables how DMax & DMin are excluded of these rules. I have no problem using them.

Moreover, the following Dlookup works perfectly and pulls the correct data:

    Obj = Nz(DLookup("Type", "MSysObjects", "Name='frmDelete'"))
    Debug.Print Obj

(frmDelete is a form)
It seems DLookup has a serious problem with Date fields.

And June7 has a valid point. If we use Cstr(), DLookup returns correct data.

If you want to investigate further, you could easily test by making a copy of MSysObjects then removing its read only attribute.

Unfortunately I don't know how to remove read only attribute of a table.

 

[Pat Hartman]

I don't mean to hijack this thread but I've been trying for some time to figure out how to get the name and date/time of the last updated object and MSysObjects isn't the solution. If anyone has a different option, I would be happy to hear it. I think at one point I tried a loop through the various collections but I think that didn't work either.

 

[deletedT]

I've been trying for some time to figure out how to get the name and date/time of the last updated object and MSysObjects isn't the solution.

No, it's not hijacking. I appreciate any kind of input concerning Last Updated object here.

Why do you think so?
It's working for me.
Do you filter Type when searching for the last updated object?

Can you share your experience here?

 

[The_Doc_Man]

Just for snorts & giggles, I looked at my Ac2010 version of MSysObjects and it says that the DateUpdated field is a Date/Time with no special properties listed.

However, that "DateUpdated" field doesn't appear to mean what you might first think it means. On my system, "DateUpdated" for MSysObjects is in 2009 even though I have certainly updated MSysObjects in 2018 by adding rows to the table. Therefore, DateUpdated may mean "saved while in Design Mode." But something else is going on there. I wrote a query of a fairly simple type to see what it would do:

SELECT MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.DateCreate, MSysObjects.Type
FROM MSysObjects
WHERE (([DateUpdate]<>[DateCreate]))
ORDER BY MSysObjects.DateUpdate DESC;

Interestingly enough, a lot of the "basic" MSysxx objects were omitted. But... this got crazy. I uploaded the attachment. Not sure where it actually got stored, but here is the crazy part. The objects near the bottom of the list have the same Create and Update date and time, yet they are retained - but the intrinsic table MSysObjects (for which Create and Update also match) was omitted. Which means that the two time fields aren't QUITE what they seem.

Follow-up: Something else is fishy here unless I have forgotten everything I ever knew. The TYPE column is wrong or else another set of constants is at work, because AcDataType (use Object Browser to find it) says acTable = 0 and acQuery = 1, but those tables are listed as type 1 (along with some of my legit tables that really ARE just app tables). So that is somehow a bit screwy.

 

[isladogs]

The Type column in MSyObjects is not the same as the acType value.
The Type and Flags values can together tell you precisely what type of object each is
See the screenshot in https://www.access-programmers.co.uk/forums/showpost.php?p=1531325&postcount=1 for an almost complete list

Tera
I'll properly test my 'read only' theory next week when I'm back home

 

[The_Doc_Man]

Some time ago I actually found the definition object that explained the flags and type used for data types using Object Browser and an explicit reference to the appropriate library. However, that was stored on my Navy computer and I retired in 2016. I thought I would not need that info so didn't forward the reference to my personal account. (Silly me for that mental lapse.) The definition exists. It is just phenomenally obscure.

 

[Pat Hartman]

I don't want to edit the MSysObjects table, I just want to be able to determine when the last database object was changed. I use a query based on MSysObjects

SELECT MSysObjects.Name, MSysObjects.DateUpdate, MSysObjects.Type, MSysObjects.Name & " -- " & MSysObjects.DateUpdate AS UpdateData
FROM MSysObjects
WHERE MSysObjects.Type In(-32768, 5,-32764, -32766)
ORDER BY MSysObjects.DateUpdate DESC;

But I have found that I can change forms and save them and this query will not reliably show the change.

The query does not include modules or tables because these seem to get updated when I don't change them.

 

[deletedT]

The query does not include modules or tables because these seem to get updated when I don't change them.

I've added a sub to my switchboard's On Unload event to determine if any object has been changed base on MSysObject table's LastUpdated field comparing to the last back up date/time.
If anything is found it shows a msg of all updated objects and backs up the database.

I haven't seen anything suspicious since I've deployed this, but will have an eye on the list to see if what you say is true for Office 365 too.

Thanks for the alert.

 

[The_Doc_Man]

Actually I dug this up from a rather circuitous method:

https://access-programmers.co.uk/forums/showthread.php?t=103811

Pat Hartman and I answered a question for a user looking at database objects. We of course each answered from a different viewpoint.

(The circuitous method was that I was following a link from StackOverflow.)