VB Code to Edit/Delete SQL Query

[Acelink]

I've been searching the forums for a little while now and have had no luck in finding the solution. I tried looking under for "VBA editing SQL Query" and other various tags.

What I am looking to do is create a VB code that edits a pre-existing crosstab query. I have been unsuccessful in finding any code that mirrors what I would like to do.

Private Sub Command0_Click()

Dim ScheduleDate As Date
Dim strSQL As String
ScheduleDate = Me.DateBox.Value

strSQL = "TRANSFORM Last(Query1.
[ListofSchedule]) AS LastOfListofSchedule" & _
"SELECT Query1.[Last_Name], Query1.[First_Name], Query1.[Employee_ID], Last(Query1.
[ListofSchedule]) AS [Total Of ListofSchedule]" & _
"FROM Query1" & _
"GROUP BY Query1.[Last_Name], Query1.[First_Name], Query1.[Employee_ID]" & _
"PIVOT Format([DateofShift], 'Short Date') IN (ScheduleDate, ScheduleDate + 1, ScheduleDate + 2, ScheduleDate + 3, ScheduleDate + 4, ScheduleDate + 5, ScheduleDate + 6);"

I want the code inside of Query1_Crosstab to be cleared and the new strSQL statement to be copied in.

The purpose for this is to create a semi-dynamic SQL statement with the use of VBA. The VBA variables will be literal when pasted into the Query's SQL code.

 

[pbaldy]

Searching on "QueryDef" should turn up the appropriate code. You would use a DAO QueryDef object.