Capture query runs with an audit table

[e8stewie]

Hello. I have a database containing 3 Make Table queries. I'd like to track each time this is performed. I'd like the query name and the date/time it was run to be captured in a separate table automatically when the query is run. I've seached through the forums and haven't found the answer. I'm hoping you all can help. I appreciate your assistance in advance. Thanks.

-Eric

 

[jackd]

Hello. I have a database containing 3 Make Table queries. I'd like to track each time this is performed. I'd like the query name and the date/time it was run to be captured in a separate table automatically when the query is run. I've seached through the forums and haven't found the answer. I'm hoping you all can help. I appreciate your assistance in advance. Thanks.

-Eric

Why do you have 3 make table queries? If you run this repeatedly, such that you want to log when it is run, you will creating many many tables. This does not seem like a prudent strategy.

Could you please describe the application -- what it is creating; How often it runs; How do you manage all the tables you create?

 

[e8stewie]

Why do you have 3 make table queries? If you run this repeatedly, such that you want to log when it is run, you will creating many many tables. This does not seem like a prudent strategy.

Could you please describe the application -- what it is creating; How often it runs; How do you manage all the tables you create?

Sure, I have some linked tables that are being queried. Those linked tables require authentication to access. I'm using the make table queries to create a table that does not require authentication. This is because the end users are in Excel and they link to the tables that do NOT require authentication. With my make table queries, I am pulling the most recent data available and making it static and safe for their consumption. Each time the make table queries are run, they overwrite the pre-existing data. I have a macro that runs in excel to refresh all the data from the Access database. What I want to be able to tell my end users is when the data where refreshed from the linked tables requiring authentication. If I can track when my make table queries were run, I can just pull in that date as that data. I hope this makes sense. If not, let me know and I will attempt to elaborate more. Thanks.

 

[jackd]

Here is a small routine that should do what you want. I suggest you test it to ensure it does what it is suppose to. I am using Acc2003. You can test the records in tblQueryLogger to determine when a query was executed.

This requires that you create a table in your local database:
<b>
' I created a table tblQueryLogger, with fields
' id autonumber PK
' RunTimeStamp Date/Time
' QueryName text
'
' This table must exist before you run the procedure.</b>


You will have to enter the real names of your queries into the array QNames. See the comments in the module code. <b>This is a one time set up</b>

'---------------------------------------------------------------------------------------
' Procedure : ExecuteQueriesAndLogInfo
' Author    : Jack
' Date      : 20-06-2012
' Purpose   :To run 3 make table queries and log their run date and time
'to tblQueryLogger
'
' I created a table tblQueryLogger, with fields
'  id  autonumber  PK
'  RunTimeStamp  Date/Time
'  QueryName    text
'
' This table must exist before you run the procedure.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub ExecuteQueriesAndLogInfo()

Dim db As DAO.Database
Dim queryLog As DAO.Recordset
Dim qNames(2) As String 'array to hold the 3 query names
Dim i As Integer
   On Error GoTo ExecuteQueriesAndLogInfo_Error

qNames(0) = "yourfirstMTqueryname"
qNames(1) = "your2ndMTqueryname"
qNames(2) = "your3rdMTqueryname"

Set db = CurrentDb
Set queryLog = db.OpenRecordset("tblQueryLogger")
Debug.Print vbCrLf & vbCrLf & Now & " Starting  query runs " & vbCrLf

For i = 0 To 2
    queryLog.AddNew
    queryLog!queryName = qNames(i)
    queryLog!runTimeStamp = Now
        Debug.Print qNames(i) & ".... " & Now 'for debugging
    db.Execute qNames(i), dbFailOnError   'This runs the MT query with qnames(i)
    queryLog.Update
Next i

queryLog.Close
   On Error GoTo 0
   Exit Sub

ExecuteQueriesAndLogInfo_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ExecuteQueriesAndLogInfo"

End Sub

 

[e8stewie]

Here is a small routine that should do what you want. I suggest you test it to ensure it does what it is suppose to. I am using Acc2003. You can test the records in tblQueryLogger to determine when a query was executed.

This requires that you create a table in your local database:

' I created a table tblQueryLogger, with fields
' id autonumber PK
' RunTimeStamp Date/Time
' QueryName text
'
' This table must exist before you run the procedure.


You will have to enter the real names of your queries into the array QNames. See the comments in the module code. This is a one time set up

'---------------------------------------------------------------------------------------
' Procedure : ExecuteQueriesAndLogInfo
' Author    : Jack
' Date      : 20-06-2012
' Purpose   :To run 3 make table queries and log their run date and time
'to tblQueryLogger
'
' I created a table tblQueryLogger, with fields
'  id  autonumber  PK
'  RunTimeStamp  Date/Time
'  QueryName    text
'
' This table must exist before you run the procedure.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub ExecuteQueriesAndLogInfo()

Dim db As DAO.Database
Dim queryLog As DAO.Recordset
Dim qNames(2) As String 'array to hold the 3 query names
Dim i As Integer
   On Error GoTo ExecuteQueriesAndLogInfo_Error

qNames(0) = "yourfirstMTqueryname"
qNames(1) = "your2ndMTqueryname"
qNames(2) = "your3rdMTqueryname"

Set db = CurrentDb
Set queryLog = db.OpenRecordset("tblQueryLogger")
Debug.Print vbCrLf & vbCrLf & Now & " Starting  query runs " & vbCrLf

For i = 0 To 2
    queryLog.AddNew
    queryLog!queryName = qNames(i)
    queryLog!runTimeStamp = Now
        Debug.Print qNames(i) & ".... " & Now 'for debugging
    db.Execute qNames(i), dbFailOnError   'This runs the MT query with qnames(i)
    queryLog.Update
Next i

queryLog.Close
   On Error GoTo 0
   Exit Sub

ExecuteQueriesAndLogInfo_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure ExecuteQueriesAndLogInfo"

End Sub

Thanks. I've added this as a module, built the table with the same name, fields/types and replaced my query names in the code. I've assigned it to run on the click of a button on a form. But nothing is happening. I'm using Access 2010. Just so I'm clear, this is supposed to run the queries as well, right? Am I using this code correctly? Thanks.

 

[xenou]

Haven't really read closely but perhaps a timestamp record in your table (with a default value of Now() ) - could that help to identify when the data was pulled? I.e. an extra field [DateCreated].

 

[jackd]

Do you know how to step through your code?
Do you have any data in the table you created?
Was anything printed in your immediate window?