Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Modules & VBA (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=12)
-   -   [SOLVED] Last updated object in a database (https://www.access-programmers.co.uk/forums/showthread.php?t=306817)

Tera 09-12-2019 10:38 PM

Last updated object in a database
 
It should be simple, but apparently I'm missing something here.

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

Code:

    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 09-12-2019 11:03 PM

Re: Last updated object in a database
 
Code:

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 09-12-2019 11:04 PM

Re: Last updated object in a database
 
Weird but this worked for me:

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

isladogs 09-12-2019 11:51 PM

Re: Last updated object in a database
 
Variation on a theme
Create a query qryMSysObjects
Code:

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

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

Tera 09-13-2019 01:12 AM

Re: Last updated object in a database
 
Quote:

Originally Posted by isladogs (Post 1640011)
Variation on a theme
Create a query qryMSysObjects
Code:

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.

Code:

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


Tera 09-13-2019 01:22 AM

Re: Last updated object in a database
 
Quote:

Originally Posted by June7 (Post 1640006)
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.

Tera 09-13-2019 01:53 AM

Re: Last updated object in a database
 
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 09-13-2019 02:44 AM

Re: Last updated object in a database
 
Quote:

Originally Posted by Tera (Post 1640018)
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

Code:

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

June7 09-13-2019 04:16 AM

Re: Last updated object in a database
 
Whatever that 'process' is, DLookup works if the date field and parameter are converted to strings. See post 3.

isladogs 09-13-2019 04:32 AM

Re: Last updated object in a database
 
Quote:

Originally Posted by June7 (Post 1640027)
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...d.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/c...lds/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
Code:

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

Tera 09-13-2019 03:41 PM

Re: Last updated object in a database
 
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:

Quote:

Originally Posted by isladogs (Post 1640021)
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:
Code:

    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.

Quote:

Originally Posted by isladogs (Post 1640021)
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 09-13-2019 05:02 PM

Re: Last updated object in a database
 
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.

Tera 09-13-2019 05:46 PM

Re: Last updated object in a database
 
Quote:

Originally Posted by Pat Hartman (Post 1640083)
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 09-13-2019 06:53 PM

Re: Last updated object in a database
 
1 Attachment(s)
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:

Code:

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 09-13-2019 11:13 PM

Re: Last updated object in a database
 
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...25&postcount=1 for an almost complete list

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


All times are GMT -8. The time now is 11:53 AM.

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