I am working with Access 2013, where I am needing to use a pass through query to pull data from my oracle database. I have a table full of account numbers that need to be queried daily ….5200 of them. My current query does work, but it takes 18 hours to run, because it takes one account number at a time and then runs the query. I am wanting to modify my code to replace my place holder [MyAcctName] with all of the accounts in the table Accounts, and then run the query. The values will need to be separated by commas to run. The account listing changes often so I need to have the ability to replace the records within the table.
Any suggestions ?
Code:
Function RunDIR()
DoCmd.SetWarnings False
Dim db As Database
Dim rs As Recordset
Dim runSQL As QueryDef
Dim MySQL As String
Dim MyStoreNum As String
Dim MyDate As String
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Accounts", dbOpenTable)
Set runSQL = db.QueryDefs("Diad_Acct_TNum")
MyDate = Format(Int(Now()) - 1, "yyyy-mm-dd")
i = 0
Do While Not (rst.EOF)
'Asign the account number for the query, using the field
MyStoreNum = ("'" & rst![Stores:] & "'")
runSQL.SQL = Replace(runSQL.SQL, "[MyAcctName]", MyStoreNum)
runSQL.SQL = Replace(runSQL.SQL, "[My_WE_Date]", MyDate)
'Run query here
' DoCmd.OpenQuery "Diad_Acct_TNum", acViewNormal, acEdit
'Append query results into results excel file
str1ExcelPath = "C:\Users\mcz0pjy\Desktop\T mobile\Automation\QryPull.xlsm"
' Dim lRecCount As Long
' lRecCount = DCount("[S_0]", "Diad_Acct_TNum")
' If lRecCount > 0 Then
Call DoCmd.TransferSpreadsheet(acExport, _
acSpreadsheetTypeExcel12, "Diad_Acct_TNum", str1ExcelPath, _
True, Data)
' End If
i = i + 1
rst.MoveNext
runSQL.SQL = Replace(runSQL.SQL, MyStoreNum, "[MyAcctName]")
runSQL.SQL = Replace(runSQL.SQL, MyDate, "[My_WE_Date]")
For Each qry In CurrentDb.QueryDefs
On Error Resume Next
DoCmd.Close acQuery, qry.Name, acSaveYes
Next
' lRecCount = 0
Loop
DoCmd.SetWarnings True
End Function
My SQL for my Pass through, I replace [MyacctName] with a record from the table Accounts, and then it loops.
Code:
SELECT
0 s_0,
"DIR"."- Operational Organization Current Perspective Detail"."Center Name" s_1,
"DIR"."- Operational Organization Current Perspective Detail"."District Num" s_2,
"DIR"."- Package Basic Detail"."Pkg Barcode Num" s_3,
"DIR"."- Standard Calendar"."Day Date" s_4,
"DIR"."- Standard Calendar"."Day of Week Name" s_5,
"DIR"."- Stop - Pickup Acct Mgmnt Detail"."PAM Acct Num" s_6,
"DIR"."- Stop Address Detail"."City Name" s_7,
"DIR"."- Stop Address Detail"."Postal Code" s_8,
"DIR"."- Stop Address Detail"."Street Name" s_9,
"DIR"."- Stop Address Detail"."Street Num" s_10,
"DIR"."- Stop Address Detail"."Street Type Cd" s_11,
"DIR"."- Stop Basic Common Detail"."Stop Type Cd" s_12,
"DIR"."- Stop Pickup Detail"."DIAD Orgnl Stop Acct Name" s_13,
0 s_14
FROM "DIR"
WHERE
(("- Stop Basic Common Detail"."Stop Type Cd" NOT IN ('01', '99')) AND ("DIR"."- Standard Calendar"."Day Date" = date '[My_WE_Date]') AND ("DIR"."- Stop Pickup Detail"."DIAD Orgnl Stop Acct Name" = [MyAcctName]))
ORDER BY 1, 15 ASC NULLS LAST, 5 ASC NULLS LAST, 6 ASC NULLS LAST, 3 ASC NULLS LAST, 2 ASC NULLS LAST, 14 ASC NULLS LAST, 12 ASC NULLS LAST, 10 ASC NULLS LAST, 8 ASC NULLS LAST, 9 ASC NULLS LAST, 4 ASC NULLS LAST, 11 ASC NULLS LAST, 7 ASC NULLS LAST, 13 ASC NULLS LAST
FETCH FIRST 10000000 ROWS ONLY