Table date not updated when job is run by scheduler (1 Viewer)

[projecttoday]

I have 2 queries scheduled through Windows to run early in the morning. The first one deletes the records from a table and the second one (a pass-through) puts records into the table from another system. I need a way to confirm that this ran successfully. The problem is the table date does not appear to get updated when the query is run by the scheduler. So

    Dim LastUpdated as Date
   LastUpdated = CurrentDb.TableDefs("tblCancel_Report_30_Days_Atlantic").Properties("Lastupdated")
    If DateValue(Me.txtRunDate) <> DateValue(LastUpdated) Then
' send message to user
    End If

gives the wrong date. I could make a table and store the update date in it but I was wondering if I'm doing something wrong here or if there's any way to avoid that.

 

[pbaldy]

My gut feeling is that property would not be updated by data getting added. I'd be more likely to check record counts, or perhaps trapping for an error in the process you use to run the queries. For instance, you can use RecordsAffected with the Execute method.

 

[projecttoday]

I guess I was mistaken thinking that the date modified was updated when the queries were run manually. Unfortunately, I need a way to determine if the records were replaced in the morning before the user runs the report and a count won't do that. The only way is to update a date in a table and I can add an update query for this to the schedule. Unfortunately if someone runs the append query manually this table won't get updated unless they run the other query too.

 

[pbaldy]

What I was thinking was more of a before/after count, which would 0/x. You could even have it email you or something. If possible, I'd have it designed in such a way as the user couldn't just run the append query. They would click on a button to run it, which would run your entire process. My users only see forms and reports, never tables or queries.

 

[projecttoday]

I don't follow your before and after. I do have a button set up for running the delete and append queries.

 

[pbaldy]

I meant that a record count on the table before the append query would return 0, one after would return the number of records. In combination, that tells you the append query ran. Your date idea is valid though, so I'd just stick with that.

 

[projecttoday]

The count is 0 only after the records are deleted which is immediately before they are appended.

 

[pbaldy]

Yes, I know. The point was if you do record counts before and after the append query runs, you get 0 and whatever, so ensuring that the first is 0 and the second isn't tells you the append query ran.

 

[smig]

try use this:

    Dim LastUpdated as Date
    Dim db as Database
 
   Set db = CurrentDb()

   LastUpdated = db.TableDefs("tblCancel_Report_30_Days_Atlantic").Properties("Lastupdated")

    If DateValue(Me.txtRunDate) <> DateValue(LastUpdated) Then
' send message to user
    End If

 

[projecttoday]

Replacing Currentdb with an explicit declaration changes the table date?

The problem with checking record counts is that the 2 queries are run one after the other when nobody is in the office.

 

[pbaldy]

The problem with checking record counts is that the 2 queries are run one after the other when nobody is in the office.

True, but it's your process, is it not? You simply grab record counts in between:

CurrentDb.Execute "DELETE ..."

VariableForBeforeAppend = DCount("*", "TableName")

CurrentDb.Execute "INSERT INTO..."

VariableForAfterAppend = DCount("*", "TableName")

You now know the record counts before and after the append.

 

[projecttoday]

Thanks for your help. I have a table where I save the date and time each table was updated and it seems to be working well.

 

[smig]

Replacing Currentdb with an explicit declaration changes the table date?

No, but I suspect it might be similar to the .RecordsAffected thing.
worth trying.