Error 3078 - engine cannot find query FOR a query that actually exists

[GeoGlick]

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

[June7]

Set joRS = CurrentDb.OpenRecordset(jsSQL)1

[ssanfu]

Maybe try

Dim joRS As DAO.Recordset   '<<< missing type
Dim jsSQL As String

    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;"

   ' Set joRS = g_DAODB.OpenRecordset(jsSQL)
    Set joRS = Currentdb.OpenRecordset(jsSQL)     '<<< use CurrentDb for testing

[GeoGlick]

Maybe try

Dim joRS As DAO.Recordset   '<<< missing type
Dim jsSQL As String

    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;"

   ' Set joRS = g_DAODB.OpenRecordset(jsSQL)
    Set joRS = Currentdb.OpenRecordset(jsSQL)     '<<< use CurrentDb for testing

Thank you for your responses but I just realized my error. I'm using the OpenDatabase method on my backend database to create g_DAODB (to create a persistent connection). Like most Access programmers do, my backend database consists of only tables and the frontend database file contains the queries (based on linked tables). Doh. Access can't find the query because it does not exist on the backend database.

[June7]

Post the query.