runtime error '-2147467259 (80004005)': ODBC-CALL failed. (1 Viewer)

[CanuckBuck]

I'm in the process of converting a .adp file format database to a .accdb file format database. It's worked fine for years as a .adp but our organization is upgrading to Windows 10 and MS Access 2019

I have a bit of code which updates the caption of a tab when a user adds or deletes a record from a subform. It works fine when adding a record but produces the runtime error when deleting a record. Does anyone know why that would be the case? Here's the bit of code.

I've marked the location where the debugger stops with the text <<<< Debugger Stops here >>>>

Public Sub SetTabCrew_Moves(Frm As Access.Form)
    Dim strSQL As String
    Dim rs As ADODB.Recordset

'This subroutine determines how many information request records are related to a specified (throuth the passing
'of the DB_Key) Milestone_Dates record.  This value is inserted into the Information_Request tab control's
'label
   
    If Not IsNull(Frm.DB_Key) Then
        Set rs = New ADODB.Recordset
        Set rs.ActiveConnection = CurrentProject.AccessConnection
        strSQL = "SELECT COUNT(*) AS [Rows] FROM [Crew_Moves] WHERE [Milestone_Dates_DB_Key] = " & Frm.DB_Key
        rs.Open strSQL
        If rs("Rows") > 0 Then  <<<< Debugger Stops here >>>>
            Frm.tabCrew_Moves.Caption = "Crew Moves (" & rs("Rows") & ")"
        Else
            Frm.tabCrew_Moves.Caption = "Crew Moves"
        End If
        Set rs = Nothing
    Else
        Frm.tabCrew_Moves.Caption = "Crew Moves"
    End If

End Sub

Thanks in advance for any assistance you can provide.

 

[The_Doc_Man]

You are moving to .ACCDB, which means you could perhaps perform a DAO test instead of an ADO test. Since you are discarding the recordset after testing it anyway, why bother with an explicit recordset. The real question is if the table Crew_Moves is visible for the purpose, can you do that test with a DCount? Declare your Rows variable as a LONG then...

Rows = DCount( "*", "[Crew_Moves]", "[Milestone_Dates_DB_Key] = " & Frm.DB_Key )
If Rows > 0 ....

This also depends on the format of Frm.DB_Key, so extra quoting might be involved, such as this example:

DCount( "*", "[Crew_Moves]", "[Milestone_Dates_DB_Key] = '" & Frm.DB_Key & "'" )

 

[CanuckBuck]

That's it!

I swear I tried that but I must have had something wrong because I couldn't get it to work for me on my own.

Thank you so much!