Change ODBC Connection of a saved query using VBA (1 Viewer)

[fernando.rosales]

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;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=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

 

[Rx_]

Please add code tags in front of and after your code.
in front of code add squarebracket open CODE squarebracket close
at end of code add squarebracket open /CODE squarebracket close

Is the external database (odbc) a SQL Server, Access, Excel, Oracle...?
Here are some previous post on ODBC to get you started:
http://www.access-programmers.co.uk/forums/showthread.php?t=217498&highlight=DSNless
http://www.access-programmers.co.uk/forums/showthread.php?t=270468
http://www.connectionstrings.com/

Basically, use some code to establish the Linked Table. Then run the query on the linked table.