CODEWITHSUNDEEP
vbams-accessoutlook
Sangster
Sangster

Reputation: 3

VBA: Interact with Access from within Outlook

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

Upvotes: 0

Views: 1109

Answers (1)

Erik A
Erik A

Reputation: 32632

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

Upvotes: 1

Related Questions