Error Opening Recordset from a linked table

[dunagh]

Hello,

I have searched online for a solution to this and haven't found anyone with my exact issue. I have a database that has a linked table from another Access database named BuildSheetFolderList. I can run queries off that table in the query builder all day long with no issues. But when I try to use the same SQL code the query builder produced in VBA code, I get the following error:

?errors(0).Number
 3078 
?errors(0).Description
The Microsoft Access database engine cannot find the input table or query ''. Make sure it exists and that its name is spelled correctly.
?errors(0).Source
DAO.Database

Following is the portion of code that crashes and is triggered when a toggle button is clicked.

Private Sub lstBuildRequests_AfterUpdate()
    Dim db As Database
    Dim requestRST As DAO.Recordset2
 
    Dim ActiveRequestsSQL As String
    Set db = DBEngine(0)(0)

    ' When this is true, and ActiveRequestsSQL is assigned the SQL in the true part, it gives the above error when opening the recordset, as indicated below.
    If Me.tglFSUArea Then
        ActiveRequestsSQL = _
              "SELECT BuildSheetFolderList.[FSU Area] AS REQ_DESC, " _
            & "BuildSheetFolderList.[Pay Crew] AS PER_FULL_NM, " _
            & "BuildSheetFolderList.[Work City], " _
            & "BuildSheetFolderList.[Work Address] AS NOTES " _
            & "FROM BuildSheetFolderList " _
            & "WHERE (((BuildSheetFolderList.ID)=" & Me.lstBuildRequests.value & "));  "
    Else
        ActiveRequestsSQL = _
              "SELECT dbo_BLD_REQ.BLD_REQ_ID, " _
            & "dbo_BLD_REQ.REQ_DESC, " _
            & "dbo_BLD_REQ.WRKSTN_QTY_CD, " _
            & "dbo_V_DEHR_PERSON_DATA.PER_FULL_NM, " _
            & "dbo_BLD_REQ.REQ_BLD_DT, " _
            & "dbo_BLD_REQ.TRGT_BLD_DT, " _
            & "dbo_BLD_REQ.ACTL_BLD_DT, " _
            & "dbo_BLD_REQ.NOTES, " _
            & "dbo_BLD_REQ.BLD_REQ_CMPLTN_DT, " _
            & "dbo_BLD_REQ.BENTLEY_REQ_FLG " _
            & "FROM ((dbo_BLD_REQ " _
            & "LEFT JOIN dbo_BLD_REQ_STAT_HIST ON dbo_BLD_REQ.BLD_REQ_ID = dbo_BLD_REQ_STAT_HIST.BLD_REQ_ID) " _
            & "LEFT JOIN dbo_BLD_REQ_WRKSTN ON dbo_BLD_REQ.BLD_REQ_ID = dbo_BLD_REQ_WRKSTN.BLD_REQ_ID) " _
            & "LEFT JOIN dbo_V_DEHR_PERSON_DATA ON dbo_BLD_REQ.RQSTR_LOGIN_ID = dbo_V_DEHR_PERSON_DATA.PER_NETWRK_LOGIN_ID " _
            & "WHERE (((dbo_BLD_REQ.BLD_REQ_ID)= " & listSelection & " ));"
    End If
    
    'This gives the '...cannot find the input table or query...'.
    Set requestRST = db.OpenRecordset(ActiveRequestsSQL, dbOpenDynaset, dbSeeChanges)
End Sub

Any help or pointers where to look is greatly appreciated. If I didn't include any crucial information forgive me a let me know. Thanks.

 

[pr2-eugin]

I am sure you would have tried this, but did you try outputting the ActiveRequestsSQL String and see if you get the right Query? Because the Error seems to have an Empty string in the OpenRecordset statement based on the error..

The Microsoft Access database engine cannot find the input table or query ''. Make sure it exists and that its name is spelled correctly.

Also try declaring the variables as..

Private Sub lstBuildRequests_AfterUpdate()
    Dim db As [COLOR=Red][B]DAO.[/B][/COLOR]Database
    Dim requestRST As DAO.Recordset2
 
    Dim ActiveRequestsSQL As String
    Set db = [COLOR=Red][B]CurrentDB[/B][/COLOR]

 

[dunagh]

Thanks for the reply pr2-eugin.

Yes. I have done a debug.print with the result of:

SELECT BuildSheetFolderList.[FSU Area] AS REQ_DESC, BuildSheetFolderList.[Pay Crew] AS PER_FULL_NM, BuildSheetFolderList.[Work City], BuildSheetFolderList.[Work Address] AS NOTES FROM BuildSheetFolderList WHERE (((BuildSheetFolderList.ID)=12));

I also tried the suggested changes but still no luck. I get the same error. :-(

 

[pr2-eugin]

How about changing it to Recordset instead of Recordset2?

 

[dunagh]

Found the problem with my code. It was an Id10t error on my part. After spending too much time looking at my code on Friday, when I came back Monday and gave it another go I realized that the line of code immediately following the line I thought was giving me an error was causing the issue. All is resolved now. Thanks for taking the time for the reply. Kicking myself in the ass for this one. :banghead:

 

[thechazm]

Taking a step back almost seems to fix everything I think I'm going to use that as a signature line Glad you got it working.