I'm trying to tweak my Access app to improve performance (over a very slow network) so I'm comparing processing time between ADODB and DAO.OpenRecordSet methods of retrieving my data. The ADODB method works fine:
jsSQL = "SELECT qDSSubjCatAct.Subject_PK"
jsSQL = jsSQL & ", qDSSubjCatAct.Subject"
jsSQL = jsSQL & ", qDSSubjCatAct.SubCategories_PK"
jsSQL = jsSQL & ", qDSSubjCatAct.Categories"
jsSQL = jsSQL & ", qDSSubjCatAct.Action_PK"
jsSQL = jsSQL & ", qDSSubjCatAct.Action"
jsSQL = jsSQL & ", qDSSubjCatAct.FundingType_FK"
jsSQL = jsSQL & ", qDSSubjCatAct.IsEquipmentRequired"
jsSQL = jsSQL & ", qDSSubjCatAct.CallType_FK"
jsSQL = jsSQL & ", qDSSubjCatAct.CatFundTypeFK"
jsSQL = jsSQL & ", qDSSubjCatAct.CatIsEquipReqd"
jsSQL = jsSQL & ", qDSSubjCatAct.ShippingAddressRequired"
jsSQL = jsSQL & ", qDSSubjCatAct.AutoGenData_FK"
jsSQL = jsSQL & " FROM qDSSubjCatAct;"
Dim joRS As ADODB.Recordset
Set joRS = New ADODB.Recordset
With joRS
.ActiveConnection = goADOConnect '- Global ADODB.Connection object
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CursorLocation = adUseClient
.Open jsSQL
Do While Not .EOF
jsRecord = .Fields(0)
jsRecord = jsRecord & ";" & .Fields(1) '- much more processing here but it's irrelevant to the problem
.MoveNext
Loop
.Close
End With
Now I tried the same thing using DAO (same database, same module, just a different Sub) and I get Error 3078 for the same query at OpenRecordSet:
Dim joRS
jsSQL = <exactly same as above>
Set joRS = g_DAODB.OpenRecordset(jsSQL)
The SQL for qDSSubjCatAct is:
SELECT tblSubject.Subject_PK, tblSubject.Subject, tblSubCategories.SubCategories_PK, tblSubCategories.Categories, tblAction.Action_PK, tblAction.Action, tblSubject.FundingType_FK, tblSubject.IsEquipmentRequired, tblSubject.CallType_FK, tblSubCategories.FundingType_FK AS CatFundTypeFK, tblSubCategories.IsEquipmentRequired AS CatIsEquipReqd, tblAction.ShippingAddressRequired, tblAction.AutoGenData_FK
FROM tblAction INNER JOIN (tblSubCategories RIGHT JOIN (tblSubject INNER JOIN tblSubjCatActXref ON tblSubject.Subject_PK = tblSubjCatActXref.Subject_FK) ON tblSubCategories.SubCategories_PK = tblSubjCatActXref.SubCategories_FK) ON tblAction.Action_PK = tblSubjCatActXref.Action_FK
ORDER BY tblSubject.Subject, tblSubCategories.Categories, tblAction.Action;
I know my g_DAODB object works because I retrieve data using a table just before I execute for qDSSubjCatAct. Must be an issue with DAO and possibly RIGHT JOINs? I need the RIGHT JOIN because not every tblSubject will have a tblSubCategories record. I'm stumped as to why it doesn't work for DAO.
Thank you for any suggestions/advice/WAGs.
Geoff