CODEWITHSUNDEEP
vbams-accessadodb
taezar.tw
taezar.tw

Reputation: 63

VBA ADODB Query Return -1

I am trying to connect to MS Access database with ADODB using VBA. When I run a query, I always gets -1 which should be 1. I have check the connection using *.uld test file and found OK. Please help.

Private Sub Form_Load()
    Dim sql As String
    Dim recdData As New ADODB.Recordset
    Dim sirb As String
    sirb = "12345"
    sql = "SELECT * FROM sirb_registration WHERE sirb = '" & sirb & "'"
    Set recdData = getResult(sql)
End Sub

Private Function getResult(sql As String) As ADODB.Recordset
    Dim db_conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Set getResult = Nothing
    
    db_conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & CStr(DLookup("db_path", "[DB_Path]")) & ";Persist Security Info=False;"
    db_conn.Open
    rs.Open sql, db_conn, adOpenDynamic, adLockOptimistic
    
    Set getResult = rs

    MsgBox getResult.RecordCount
    
    rs.Close
    db_conn.Close
    Set db_conn = Nothing
    Set rs = Nothing
End Function

Below is my Database sirb_registration sample image. Table sirb_registration

Below images are Query and the Query results. Query

Query Result

Upvotes: 1

Views: 326

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

According to Microsoft's documentation here, even for adOpenDynamic RecordCount is sometimes always -1:

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.

Unfortunately I have not been able to find out what specifically "depending on the data source" means.

It may well be therefore that your test is here meaningless. Have you simply tried reading from the recordset?

Upvotes: 1

Related Questions