VBA: Interact with Access from within Outlook

Question

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

Is `EmailID` a string? I wouldn't use `Replace` on the parameter - just use [an actual parameterized query](https://stackoverflow.com/q/10352211/4088852)... — Comintern, Nov 12 '18 at 02:54
The email ID is stored within Access as a string. Setting a break point at `on error resume next` and calling `?sql` in the immediate window yields an SQL string that when executed in the Access query designer returns the correct result.I realise parameterised queries would be better however I do not like them cluttering up the the global namespace and don't want to bother with creating and deleting them all the time. I find simply writing out the SQL string and replacing placeholder text to be easy to read and maintain. Neither security nor efficiency are important here. — Sangster, Nov 12 '18 at 03:28
Cluttering the global namespace? I'm not sure I know what you mean by that. The parameter can be local. *Comment out* the `On Error Resume Next` and see if you're getting an error. — Comintern, Nov 12 '18 at 03:30
No errors are occurring with that line commented out. I just get a recordset with no rows in it. My experience with parameterised queries is that they're saved with the database, I did not know they could be local. If they require more lines of code than my approach I'd avoid them anyway because I cannot fold code away in VBA, and I find it hard to read when there's too much in the file. — Sangster, Nov 12 '18 at 03:35
The problem seems to be with `rs.RecordCount`, which always returns `-1`. If I replace `if rs.RecordCount > 0` with `if Not rs.EOF` then there are indeed rows returned and I can access the correct data by it's index (which should have been 0 and not 1). So the code works but the `rs.RecordCount` does not, even if I use `rs.MoveLast` followed by `rs.MoveFirst`. Can anyone explain why this is? — Sangster, Nov 12 '18 at 04:07

Erik A · Accepted Answer · 2018-11-15T07:00:54.220

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

Thanks for your feedback. I appreciate the info on why the `.RecordCount` is -1, though I am not a fan of VBA's approach to error handling as the syntax looks nasty to me and without code folding I prefer to use every opportunity to reduce lines of code. When error handling matters I prefer to test for error numbers then return a fail constant and message. In this case it isn't of concern, so fewer lines wins. If you're happy to amend `rs(1).Value` to `rs(0).Value` and remove the `On Error Resume Next` line completely I'll mark your response as the answer. — Sangster, Nov 15 '18 at 00:26

VBA: Interact with Access from within Outlook

1 Answers1