lookup record and compare (2003 sp2)

Topic

New Reply

I have been trying to build a simple function that looks at the MSysObjects table for a specific Id and compares the DateUpdate field to the current date. If the DateUpdate date is not equal to today the function returns true. Pretty simple. I thought I could do this with a SQL statement and this is as far as I get:

Public Function CheckTableUpdate(lngID As Long)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = “SELECT IIf(Format([DateUpdate],””mmddyyy””)<Format(Date()," _
& """mmddyyy""),[DateUpdate],"") AS DtUpdate" _
& "FROM MSysObjects;"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
At this line I get an error Object variable of With Block variable not set.

The rest would be an if statement to check the DtUpdate field for null.

Any help would be appreciated.

Reply | Quote

Replies

1) You never set the variable db. Add the line

Set db = CurrentDb

2) Although you took care to double the quotes around the formats, you forgot to do that for the empty string. Try

strSQL = "SELECT IIf(DateUpdate < Date(), DateUpdate, '') AS DtUpdate FROM MSysObjects"

3) Since the code is incomplete, I don’t know what the role of lngID is, and how it will work.

Reply | Quote

You have left out the line

Set db =CurrentDB

Reply | Quote

I always miss the little things. Thanks guys.

I formated Date() and DateUpdate because the time is included in DateUpdate and is therefore always less than Date(). This was the only way I could get it to work.

lngID is the Id from MSysObjects that I would pass as an argument when I call the function.

But I still need more help. I thought I could use an IF statement to check for the Null.

If IsNull(rst.DtUpdate) Then
CheckTableUpdate = True
Else
MsgBox “Records are current.”
End If
End function

Now I get an error at .DtUpdate Method or data menber not found. I figured I was in trouble when DtUpdate did not appear in the list. How do I refer to it?

Reply | Quote

I don’t understand what you’re doing. The DateUpdate field in the MSysObjects table contains the date the design of the table (or other database object) was last updated, it has nothing to do with the date of the records in the table.

And since you never assign Null to DtUpdate, how can you expect it ever to be Null?

Reply | Quote

The SQL produces Null when DateUpdate equals Date(). If I copy the SQL into a query and add the Id field and ID it produces one record with one field. If the result is Null in the DtUpdate field my function will be true and I will know the procedure has run when I call the function.

My procedure to update the table completely empties the table and repopulates it with current data from the update file. Perhaps this constitutes the design change you speak of, because the DateUpdate field changes each time the procedure runs. Am I making any sense yet?

Reply | Quote

The SQL as posted by you produces an empty string “” if DateUpdate is the current date. That is *not* the same as Null!

Do you use a delete query statement followed by an append query, or do you recreate the table by using a make-table query?

Reply | Quote

Ah ha! You got me on that one. I forgot about the empty string not being the same as null. I was hoping not to uncover all my ignorance but now that I have I can only get smarter…with your help of course.

When I first wrote the IIF statement I thought of assinging “Null” but didn’t think that would work in a date field. What would you do?

I do use a delete query and then an append query to repopulate the table.

Reply | Quote

There is no objection against using Null. (In fact, a date field can be Null but it cannot be an empty string, but you’re not populating a field in a table here, you’re returning a value in a recordset, so anything goes)

I don’t understand why the DateUpdate value of the table would change when you run a delete query and an append query against the table – it doesn’t work that way for me. But if it consistently works for you, that’s fine.

Reply | Quote

Perhaps the change to the modification date is an Access 2003 thing. Aren’t you using 2002? Anyway, the modification date is consistently changed each time I empty and repopulate. But I still need to point to the DtUpdate field to test it. How can I do that? Or is there just a better approach to the matter.

My updating is an automated process that affects multiple databases and multiple tables in each database. I also have an automated backup procedure that compacts and copies each database each day. I wanted to incorporate the test of the modification dates within that process and then produce a report listing the results for each table. Right now I just send myself an email if something fails.

Reply | Quote

I’d simply open a recordset on the MSysObjects table, then loop through the records and compare the DateUpdate field in each record to the current date, and act as needed.

…
Set rst = db.OpenRecordset(“MSysObjects”, dbOpenDynaset)

Do While Not rst.EOF
If rst!DateUpdate < Date Then
…
Else
…
End If
rst.MoveNext
Loop

rst.Close
…

(Yes, I'm still using Access 2002 at home)

Reply | Quote

I am really weak on the Do Loop. So if I have 6 non-sequencial Object Id’s how could I loop just through the 6?

Reply | Quote

You could restrict the recordset to those IDs:

Dim strSQL As String
strSQL = “SELECT * FROM MSysObjects WHERE ID In (2, 3, 5, 8, 13, 21)”
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Do While Not rst.EOF
…

Or you could loop through all records and add a check:

Set rst = db.OpenRecordset(“MSysObjects”, dbOpenDynaset)
Do While Not rst.EOF
Select Case rst!ID
Case 2, 3, 5, 8, 13, 21
…
Case Else
…
End Select
…

Reply | Quote

Once again Hans, you have been a tremendous help, and you stay up late too.

Reply | Quote

Yes, it’s time to get some sleep…

Reply | Quote

BTW instead of the MSysObjects table, you could also use the CurrentData.AllTables collection and look at the Name and DateModified properties of each item.

Reply | Quote

Not that this is any earth shattering breakthrough in VBA coding, but this is what I ended with for my reusable function:

Public Function CheckTableUpdate(lngID As Long)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dte As Date
Set db = CurrentDb
strSQL = “SELECT MSysObjects.DateUpdate, MSysObjects.Id ” _
& “FROM MSysObjects ” _
& “WHERE MSysObjects.Id= ” & lngID & “”
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
dte = Format(rst!DateUpdate, “mm/dd/yy”)
If dte Date Then
CheckTableUpdate = True
Else
CheckTableUpdate = False
End If
rst.Close
End Function

This does what I need it to do, simply return true or false, that is, if the MSysObjects.ID value does not change. It did change on me during testing and returned a “No Current Data” error. I thought it was a code issue until I checked the table and found Access had updated the ID value to a new number. I thought that was strange since I had done nothing to affect the table the record pointed to.

Reply | Quote

Perhaps you could use the Name field instead of the ID?

Reply | Quote

The table name of the particular table I update appears twice in the MsysObjects table. Once with a long negative number ID and once with a three digit positive number. Oddly enough they do not hold the same date in the DateUpdate field.

By the way Hans, I tried working with the Loop but I just can’t seem to catch on to it.

Reply | Quote

You should be able to look at the Type and Flags fields to see which ones are “normal” tables.

Standard tables have Type = 1, linked tables have Type = 6.

For tables, Flags can be a combination of one or more of the following values:
Deleted or temp table = 3
Hidden table = 8
Linked table = 2097152
System table = 2 or -2147483648
Standard table = 0

Reply | Quote

Yes, one of the Type values for my table is 1. I could use that in combination with the Name. That should be more reliable.

I am curious why there is so much syntax difference between SQL in VBA and SQL in a query. I thought all this was managed by Jet? (Not that I understand Jet…).

Reply | Quote

> why there is so much syntax difference between SQL in VBA and SQL in a query

In what sense?

Reply | Quote

I have read some on SQL, but honestly do not have the time to learn it in a proper way, so I cheat. To build my SQL I build a regular query, switch to SQL view, and then copy it into my VBA Editor. In my query to format the date I used “mmddyy” to get just the date without the time. In VBA the same syntax produced 12:00 AM, no relation to the actual value in the DateUpdate field. Adding the ‘/’ gave me the correct date value without the time, and that was a guess.

In the query you end the statement with a semicolon, but drop it in VBA. Parentheses don’t seem to be important either, although my guess is Access adds them for no apparent reason in the query. When I was experimenting with all this I used an ID value as criteria which Access wrote as WHERE (((MSysObjects.Id)=464)); Why double parenthese? No parenthese needed VBA.

Stuff like that.

Reply | Quote

I don’t understand why “mmddyy” gave you 12:00 AM.

The semi-colon at the end of an SQL string is optional. Access always adds it when you design a query, but if you switch to SQL view and remove the semicolon, the query will still work. (Access will add it if you switch to design view again). Similarly, an SQL string created in VBA will work with and without a semicolon at the end.

Access adds an enormous amount of superfluous parentheses, in particular in the WHERE clause. I guess that this was done to make it easier to add and remove conditions. You can keep the parentheses in VBA or remove them – it’s a matter of personal preference.

Reply | Quote

Well Hans, I now understand your warning about messing with system tables. The code I posted using MSysObjects.ID is not reliable. Thanks to your post 682426 I was able to use a parameter query to parse the MSysObjects table, receive consistant results, and have a reusable function as well. I thought this would never end.

Reply | Quote

Chuck

Just a s a point of interest what do you use this reusable function for?

John

Reply | Quote