Change ODBC Connection of a saved query using VBA

Good morning people,



I have some reports that I run on a weekly basis and that I am trying to automate.

I have a make table query that is tied to a SQL Query (which is on the code below).

I was successful in changing the SQL Query code using VBA to my needs but I am not able to connect using different ODBC connection on each piece of code. And yes I know I can just make more saved Querys and automatically setting them with the correct connection but I would like to know how I can change using vba. By the way I am using .connect but it is not making the changes.



Public Sub run_query_and_savereport()

Dim m As String

Dim d As String

Dim y As String

Dim db As DAO.Database

Dim SQLAGEMEMO As String

m = Month(Now)

d = Day(Now)

y = Year(Now)

'SWDC Age MEMO

SQLAGEMEMO = "SELECT a.dc_id, a.wh_id, a.order_type, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "case when a.order_type ='F' then 'Flow' else 'Hotel' end FLOW_HOTEL , " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "a.event_description, a.department, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "case when dp.department > '4999' then concat('dd','''s') else 'Ross' end CHAIN, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "dv.division as Division, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "DV.DIVISION_NAME as DIV_NAME, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) <=3 then Sum(a.unit_qty) else 0 end Age_0_to_3, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) in (4,5,6,7) then Sum(a.unit_qty) else 0 end Age_4_to_7, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) in (8,9,10) then Sum(a.unit_qty) else 0 end Age_8_to_10, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) in (11,12,13,14,15) then Sum(a.unit_qty) else 0 end Age_11_to_15, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) in (16,17,18,19,20,21,22,23,24,25,26,27,28,29,30) then Sum(a.unit_qty) else 0 end Age_16_to_30, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Case when round(a.Age_days,0) > 30 then Sum(a.unit_qty) else 0 end Age_Greater_30, " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Sum(a.unit_qty) Total " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "FROM division dv, groups g, department dp, class cl, repadmin.ross_daily_aged_memo a " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "WHERE dv.facility_id = 'PR' " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND dv.facility_id = dp.facility_id " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND dv.facility_id = cl.facility_id " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND dv.facility_id = g.facility_id " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND DV.DIVISION = G.DIVISION " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND G.GROUP_NBR = DP.GROUP_NBR " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND DP.DEPARTMENT = CL.DEPARTMENT " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND concat(dp.department,cl.class) = concat(a.department,a.class) " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "AND a.dc_id = 'SEDC' " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "and dp.department <='4999' " & vbCrLf

SQLAGEMEMO = SQLAGEMEMO & "Group by a.dc_id, a.wh_id, a.order_type, a.event_description, a.Age_days, a.department, dv.division, DV.DIVISION_NAME, dp.department" & vbCrLf

Set db = CurrentDb

db.Connect = "ODBC;DSN=RDM SEDC;UID=APPSUP;PWD=APPSUP;DBQ=SEDC;DBA=W;APA=T;FE N=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CS R=F;PFC=10;TLO=0;"

db.QueryDefs("sqlsedcagememo").SQL = SQLAGEMEMO

DoCmd.SetWarnings warningson:=False

DoCmd.OpenQuery "sqlmaketableagememo"

DoCmd.OutputTo acOutputReport, "Network_Memo_Allocated_by_Product_Group", acFormatPDF, "C:\Users\495170\Documents\Division Aging auto-send\SW_Memo_Allocated_by_Division_Group " & m & "-" & d & "-" & y & ".pdf"

DoCmd.SetWarnings warningson:=True

Set db = Nothing



End Sub

Change ODBC Connection of a saved query using VBA