Write SQL of query to Memo Field

[bkf7911]

I have a query that will change daily and would like to record the current SQL behind the query with every change, and store the SQL for the current day in either a memo field, a text file, or an excel file (Memo Best). Is there any code that can automate this?

 

[dhookom]

You didn't state what changes the sql on a daily basis. Prior to the change, you can run some code that grabs the SQL property of the saved query like:

Public Function GetQuerySQL(strQuery as String)
  Dim db as DAO.Database
  Dim qd as DAO.QueryDef
  Set db = CurrentDb
  Set qd = db.QueryDefs(strQuery)
  GetQuerySQL = qd.SQL
End Function

You could probably use this function in an append query to add records to a table:

INSERT INTO tblQuerySQL ( QueryName, QuerySQL, QueryDate )
SELECT msysObjects.Name, GetQuerySQL([Name]) AS Expr1, Now() AS Expr2
FROM msysObjects
WHERE (((msysObjects.Name)="YourQueryNameHere"));

Duane
Hook'D on Access
MS Access MVP

 

[bkf7911]

I'm changing the dates to a variety of different dates, based on the current day (prior week begin/end; prior month; prior day). Then i reference those values in a passthrough, run the passthrough and export the results. I need to also record the exact SQL used, so thought there may be code for this.... and there is. Thanks for yours.... it works PERFECTLY.