rs.rowcount = -1

Has been a while since I've back to VBA...
Cant seem to  get a record count from a query to mysql

all the code involved is below

    Debug.Print rs.RowCount

Keeps returning - 1 no matter what I tried

maybe my connection is wrong or i call the query wrongly?

did try suggested movefirst  , movelast with no success
Dim oConn As ADODB.Connection
Sub last_update()

Dim i As Integer, cur_id As Integer, lastupdate As Date
Set rs = New ADODB.Recordset
ConnectDB
With Sheets("DO Control")
i = 3
    Do While i < .UsedRange.Rows.Count + 1
        cur_id = .Cells(i, 1).Value
        strSQL = " SELECT MAX(t1.last_updated) FROM " & _
        " (SELECT MAX(`created`) AS last_updated FROM `inventory`.`packing_list_items`WHERE (`packing_list_id` = " & cur_id & ")  " & _
        " UNION ALL SELECT MAX(`created`) FROM `inventory`.`stock_out_actions` WHERE (`packing_list_id` = " & cur_id & ")  " & _
        " UNION ALL SELECT MAX(`created`) FROM `inventory`.`pick_list_items` WHERE (`packing_list_id` = " & cur_id & ")) AS t1; "
   
    rs.CursorLocation = adUseClient
    rs.Open strSQL, oConn, adOpenDynamic, adLockReadOnly
    Debug.Print rs.RowCount
        lastupdate = rs.GetRows()(0, 0)
        .Cells(i, 2) = lastupdate
    rs.Close
    i = i + 1
    Loop

End With

End Sub



Private Sub ConnectDB()
    Set oConn = New ADODB.Connection
    oConn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
        "SERVER=somehost;" & _
        "DATABASE=inventory;" & _
        "USER=someuser;" & _
        "PASSWORD=somepass;" & _
        "Option=3;"
End Sub

Open in new window

LVL 1
CMDAIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ritesh RatnaBusiness Analyst/SharePoint ConsultantCommented:
Obviously you would get the Record Count as 1.

Check you Query, what you are querying is "SELECT MAX(t1.last_updated) FROM ................".

This returns you the Maximum Value avaialable for "t1.last_updated". Obviously there would only be 1 Maximum value and therefore just 1 record.

Check and modify your SQL Query as per your requiremnt.
koevoetsfjmCommented:
Hi

Because you've opened the recorset with adOpenDynamic you will not get a recordcount.

Better is to use this method to see if records are returned from your query:

oRs.Open Sql, Connection.........

if (not oRs.EOF) and (not oRs.EOF) then
   do until ors.EOF
        '--- do what you want to do
        ors.movenext
  loop
end if


goodluck,. Frans.
koevoetsfjmCommented:
So this would be your code snippet:

rs.Open strSQL, oConn, adOpenDynamic, adLockReadOnly
Debug.Print rs.RowCount
lastupdate = rs.GetRows()(0, 0)
.Cells(i, 2) = lastupdate
rs.Close

becomes:
rs.Open strSQL, oConn, adOpenDynamic, adLockReadOnly
if (rs.EOF and rs.BOF) then
  '-- the recordset is at begin and end simultaniously, only can happen if record is not found.
   debug.print ("There are no records !")
else
   ors.MoveFirst  
   lastupdate = rs.Fields(0).value    
   .Cells(i, 2) = lastupdate
    rs.Close
end if
CMDAIAuthor Commented:
RiteshRatna:
THis query would return 1 or 0 records, so here I would simply like to know if there is any records returned, but at the same time if i know how to find our how many records returned would be great.

koevoetsfjm:
Weird thing about EOF and BOF is both remain FALSE whether 1 record is returned or 0.


 
koevoetsfjmCommented:
Hi

This weird behaviour may be explained by the fact that you're using a client-side cursor:
You can check if the recordset supports the recordcount property (snippet 1)
if it does not support recordcount, you may consider changing the way you handle the cursor (snippet 2)

 

'-- How to check if Recordcount is supported:
if rs.Supports(adApproxPosition)=true then msgbox "Recordcount is supported"

'-- New Recordset definition
rs.CursorLocation = adUseServer '-- Default ADO
rs.Open strSQL, oConn, adOpenStatic, adLockReadOnly  

let me know if this works for you.
regards,
.Frans.

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.