We help IT Professionals succeed at work.
Get Started

rs.rowcount = -1

983 Views
Last Modified: 2012-05-10
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

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE