I have managed to borrow a piece of code from the internet for running a pass through query via VBA as below. As I have read that a pass thorugh query would be faster than using a query from within the [INVOICE] linked table I would like to use the 'temporary' queries results in a pop up form of which has criteria. However this code needs to be run regularly with different criteria but once the querydef has been created it say that 'this query already exists'.
How can I write the code so it allows to create a temporary query or overwrite an existing saved query. I really am trying to shave off the current time it takes to load the results from the Sage database but I am sure you all know that Sage isn't the fastest when querying from it. Any tips on how I can speed up my query from Sage would be much appreciated.
Thank you in advance
Dim strCompany As String
Dim strYearStart As String
strCompany = Me.Combo3
strYearStart = "'2011-1-1'"
Dim dabs As DAO.Database
Dim qdef As DAO.QueryDef
Dim recs As DAO.Recordset
Set dabs = CurrentDb
Set qdef = dabs.CreateQueryDef("qry3Pass")
qdef.Connect = "ODBC;DSN=SageLine50v17;UID=***;PWD=***;"
qdef.SQL = "SELECT INVOICE.ACCOUNT_REF, INVOICE.INVOICE_NUMBER, INVOICE.INVOICE_DATE, INVOICE.ITEMS_NET " & vbCrLf & _
"FROM INVOICE " & vbCrLf & _
"WHERE INVOICE.ACCOUNT_REF=" & strCompany & "AND INVOICE.INVOICE_DATE > '2011-1-1'"
qdef.ReturnsRecords = True
'Set recs = qdef.OpenRecordset(dbOpenSnapshot)
Me.frm2Pass.Form.RecordSource = "qry3Pass"