Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Write SQL of query to Memo Field

Status
Not open for further replies.

bkf7911

MIS
Mar 20, 2007
83
US
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?
 
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:
Code:
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:
Code:
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top