SQL ...speed up the opening
VB Code:
sSQL = "SELECT DISTINCT "
sSQL = sSQL & "QTempCredit.Credit_Note_Number, "
sSQL = sSQL & "QTempCredit.Employer_Ref, "
sSQL = sSQL & "QTempCredit.Client_Ref, "
sSQL = sSQL & "QTempCredit.Client_Name, "
sSQL = sSQL & "QTempCredit.Personnel_Ref, "
sSQL = sSQL & "QTempCredit.Dept_Description, "
sSQL = sSQL & "QTempCredit.Division_Description, "
sSQL = sSQL & "QTempCredit.Timesheet_Number, "
sSQL = sSQL & "QTempCredit.Credit_Note_Date, "
sSQL = sSQL & "QPSTran.[Acctg Date], "
sSQL = sSQL & "QTempCredit.Details, "
sSQL = sSQL & "QTempCredit.Description, "
sSQL = sSQL & "QTempCredit.Credit_Note_Net, "
sSQL = sSQL & "QTempCredit.Credit_Note_VAT, "
sSQL = sSQL & "QTempCredit.Credit_Note_Total, "
sSQL = sSQL & "QTempCredit.Page, "
sSQL = sSQL & "QTempCredit.Line "
sSQL = sSQL & "FROM QPSTran "
sSQL = sSQL & "INNER JOIN QTempCredit ON (QPSTran.Customer = QTempCredit.Client_Ref) "
sSQL = sSQL & "AND (QPSTran.[Item ID] = QTempCredit.Credit_Note_Number) "
sSQL = sSQL & (QTempCredit.Credit_Note_Date >= '20030501' sSQL = sSQL & AND QTempCredit.Credit_Note_Date <= '20030626') AND (QTempCredit.Client_Ref = 'GAME02')
sSQL = sSQL & "ORDER BY QTempCredit.Credit_Note_Number, QTempCredit.Page, QTempCredit.Line"
rstCrNote.Open sSQL, cnSQL, adOpenForwardOnly, adLockReadOnly
I am using ADO to connect to an Access Table which has a couple of linked tables in it (the reason is there is Tables coming from 2 different SQL Databases) QTempCredit is a Query as is QPSTran.
the problem is it is taking so long to open the recordset. Am i missing something is there someway to speed this up???
Thanks
in advance:confused: