Pfthb

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