Vfrdtyky

I am trying to create some custom buttons in Outlook that interact with a table contained within an Access database. So far I have my buttons working in Outlook, running code that instantiates a custom data access class which in turn handles opening and closing the connection to the database. So far as I can tell, this much works.

However from this class I cannot even perform a simple select query. Can anyone help me understand why the code below might not work? I always end out with a recordset that has no rows but if I run the same sql using the Access query designer it works fine.

Public Function GetJobID(ByVal xEmailID As String) As Integer

'Returns the JobID associated with a given EmailID from the email link table.

'Returns a fail constant if no link exists.

    Dim rs As ADODB.Recordset

    Dim sql As String



    'Exit if not connected.

    'Cast to boolean because VBA doesn't recognise connection state integer as boolean.

    If Not CBool(mConn.State) Then

        GetJobID = RESULT_FAIL_INTEGER

        Exit Function

    End If



    sql = "SELECT [JobID] FROM [EMAIL_LINK_TABLE] WHERE [EmailID]='xEmailID'"

    sql = Replace(sql, "EMAIL_LINK_TABLE", EMAIL_LINK_TABLE)

    sql = Replace(sql, "xEmailID", xEmailID)



    On Error Resume Next

    Set rs = mConn.Execute(sql)



    If rs.RecordCount > 0 Then

        GetJobID = rs(1).Value

    Else

        GetJobID = RESULT_FAIL_INTEGER

    End If



End Function

I see you've tracked down the issue to .RecordCount returning -1.

This is standard behavior for dynamic cursors, from the docs:

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

Of course, you can modify your code to use a static cursor, but that will impact performance. Instead, to test if there are records in your recordset, use .EOF (a method returning a boolean to indicate if the recordset is currently at the end of the file). That will save your code from having to load all records, when only loading the first one is required:

Public Function GetJobID(ByVal xEmailID As String) As Integer

'Returns the JobID associated with a given EmailID from the email link table.

'Returns a fail constant if no link exists.

    Dim rs As ADODB.Recordset

    Dim sql As String



    'Exit if not connected.

    'Cast to boolean because VBA doesn't recognise connection state integer as boolean.

    If Not CBool(mConn.State) Then

        GetJobID = RESULT_FAIL_INTEGER

        Exit Function

    End If



    sql = "SELECT [JobID] FROM [EMAIL_LINK_TABLE] WHERE [EmailID]='xEmailID'"

    sql = Replace(sql, "EMAIL_LINK_TABLE", EMAIL_LINK_TABLE)

    sql = Replace(sql, "xEmailID", xEmailID)



    On Error Resume Next 

    Set rs = mConn.Execute(sql)



    If Not rs.EOF Then

        GetJobID = rs(0).Value

    Else

        GetJobID = RESULT_FAIL_INTEGER

    End If



End Function