ADODB.RecordSet is not returning query results in MS Access 365 but same query does i

[jac in Lakewood]

I am using the following code. The problem is that the rst.recordcount is showing -1. When I take that same query in Access. it brings back two rows. I have MS Access 365 front end and SQL 2017 backend.

The current.connection beginning shows:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;

I am befuddled as to why this is not working.

Thanks for any insights

Dim strSQL As String, strSQLWHERE As String
    Dim rst As ADODB.Recordset
    Dim strFolderPath As String
 
    Set rst = New ADODB.Recordset
 
    strSQL = "SELECT tblHyperlinks.* FROM tblHyperlinks WHERE Doc_Version_ID = " & Me!Doc_Version_ID
 
 rst.Open strSQL, CurrentProject.Connection
 
 If rst.RecordCount <= 0 Then
 
ELSE
'' Need to be here
End IF

[NeoPa]

Hi Jac.

Welcome to Bytes.com.

I suspect this is because a Recordset will typically not know the full count until it has processed through getting all the records into its local buffer. Until it knows what the count is it returns -1.

[jac in Lakewood]

Thanks, NeoPa. I tried inserting a rst.movelast command before the record count and go the error message Run-Time Error '3021;

Either BOF or EOF is True or the current record has been deleted. Requested operation requires a current record.

[NeoPa]

I'm sorry. I don't have a lot of experience using ADODB, and no time now to gain it or look into it more deeply.

Is there any reason why you're using ADODB for an ACE table? Seems a little counter-intuitive to me if I'm honest.

If using the more native DAO doesn't appeal to you then I would suggest reading all the notes from the ADODB.Open() help page and considering what may be the cause of the behaviour.