How to change MS Query via VBA
 

Hi,

The following codes excecutes of but doesn't do what I expect. I want it to
change the MS Query but it remains as is after I run this code. By changing
the sSQL statements I expect the query to change. Any ideas?



Sub Change_MS_Query()
The_Path = "G:\00_cen\Oth\Inventory Planning Reports\POL\"
Access_Filename = "POL"
'Both Dept & Report Period
sSQL = "SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Report_Periods INNER JOIN (Tbl_Dept_No INNER
JOIN tbl_Comm_Data ON Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO) ON
Tbl_Report_Periods.REPORT_PERIOD = tbl_Comm_Data.REPORT_PERIOD;"
'Dept only
'sSQL = "SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Dept_No INNER JOIN tbl_Comm_Data ON
Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO;"

Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
DBFile = The_Path & Access_Filename & ".mdb"
ConString = "ODBC;DSN=MS Access Database;DBQ=" & DBFile
QueryString = sSQL

With PTCache
.Connection = ConString
.CommandText = QueryString
End With
Debug.Print PTCache.CommandText
MsgBox PTCache.CommandText
End Sub


--
Andrew
211108

Option Compare Database

Private Sub Command0_Click()
Dim qdfNew As DAO.QueryDef
Dim strSQL As String

'Both Dept & Report Period
sSQL = "SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Report_Periods INNER JOIN (Tbl_Dept_No INNER
JOIN tbl_Comm_Data ON Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO) ON
Tbl_Report_Periods.REPORT_PERIOD = tbl_Comm_Data.REPORT_PERIOD;"
'Dept only
'sSQL = "SELECT tbl_Comm_Data.[Commitment Status],
tbl_Comm_Data.CURR_DATE FROM Tbl_Dept_No INNER JOIN tbl_Comm_Data ON
Tbl_Dept_No.The_Dept = tbl_Comm_Data.DEPT_NO;"

Set qdfNew = CurrentDb.CreateQueryDef("qry_Comm_Data", sSQL)


End Sub

--
Andrew