Tom,
This is in follow up to earlier issue. I am having serious delays in pulling queries that less than a month old. Sometimes it takes hours to pull the data. I have played around with the Optimizer settings and still have serious delays. I let optimizer refresh All data, set From to include UNOPTIMIZED but still have serious delay issues. Some of the data is less than 30 days old I am trying to retrieve.
Maybe there is something wrong with my Query structure. Can you help? This is part of a larger macro that gets the proper variables. All variables have been tested.
Sub GetTempData()
Dim todaysDate As Date
'todaysDate = Format(todaysDate, "'YYYY-MM-DD'")
' GetTempData Macro
'getInvNumbers
getInvNumbersBasedOnChkBox
If invNum = "'" Then
GoTo Quit:
End If
'Suppress Screen Refreshs while Working
Application.ScreenUpdating = False
'Error Handler
'MsgBox "invNum:" & invNum
On Error GoTo Quit:
With Sheets(3).ListObjects.Add(SourceType:=0, Source:=Array(Array( _
"ODBC;DSN=QuickBooks Data;SERVER=QODBC;OptimizerDBFolder=D:\QODBC Optimizer;OptimizerAllowDirtyReads=D;OptimizerSyncAfterUpdate=Y;Syn" _
), Array("cFromOtherTables=N")), Destination:=Sheets(3).Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT InvoiceLine.RefNumber, InvoiceLine.TxnDate, InvoiceLine.TermsRefFullName, InvoiceLine.DueDate, InvoiceLine.ShipDate, InvoiceLine.ShipMethodRefFullName, InvoiceLine.Memo, InvoiceLine.PONumber, Invoi" _
, _
"ceLine.SalesRepRefFullName, InvoiceLine.FOB, Customer.BillAddressAddr1, Customer.LastName, Customer.BillAddressAddr2, Customer.BillAddressAddr3, Customer.BillAddressAddr4, Customer.BillAddressCity, Cu" _
, _
"stomer.BillAddressState, Customer.BillAddressPostalCode, Customer.BillAddressCountry, Customer.Phone, Customer.Email, Customer.ShipAddressAddr1, Customer.ShipAddressAddr2, Customer.ShipAddressAddr3, C" _
, _
"ustomer.ShipAddressAddr4, Customer.ShipAddressCity, Customer.ShipAddressState, Customer.ShipAddressPostalCode, Customer.ShipAddressCountry, Customer.Phone, Customer.Email, InvoiceLine.Subtotal, Invoic" _
, _
"eLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc, InvoiceLine.CustomFieldInvoiceLineCOLOR, InvoiceLine.CustomFieldInvoiceLineSIZE, InvoiceLine.CustomFieldInvoiceLineUPC, InvoiceLine.InvoiceLineRate," _
, _
" InvoiceLine.InvoiceLineQuantity, InvoiceLine.InvoiceLineAmount" & Chr(13) & "" & Chr(10) & "FROM Customer Customer, InvoiceLine InvoiceLine" & Chr(13) & "" & Chr(10) & "WHERE InvoiceLine.CustomerRefListID = Customer.ListID AND ((InvoiceLine.RefNumber In (" _
, " " & invNum & ")))")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_Query_from_QuickBooks_Data39"
.Refresh BackgroundQuery:=False
End With
'NOW Copy Columns over to SHEET 2 For Final Format
copyDataFromTempToFinal
'End Copy Columns
'NOW we Rename All of the Column Headings
renameColumnNames
'End
'Turn Screen Refresh Back ON
Application.ScreenUpdating = True
'Now Present Message:
emailFileMessage
Quit:
End Sub
THanks |
