Returning Recordset count from SQL using Excel VBA

Hi -

I have this macro that takes info from an Excel sheet and updates SQL records.  Works great - but my msgbox always displays a count of 1 record that is going to be updated, even though it actually will update however many fit the 'where' parameter.  I've been googling the heck out of this and have tried so many move last, move first, aduseclient, use static..... I could go on all day.

Any suggestions are greatly appreciated!

Private Sub CommandButton3_Click()

Dim dte As String
Dim loc As String
Dim con As Object
Dim aa As String
Dim NoOfDataRows As String
Dim rs As New Recordset

Set con = CreateObject("ADODB.Connection")
con.connectionstring = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=BTS;Initial Catalog=vmfg; password=m3talfab"
   
    dte = Worksheets("sheet1").Range("d1").Value
    loc = Worksheets("sheet1").Range("d3").Value
sSQL = "update location set count_date = '" & dte & "' where id like '" & loc & "%' "
con.Open

NoOfDataRows = "select count(*) from location where id like'" & loc & "%'"

rs.Open NoOfDataRows, con, adUseClient
rs.MoveLast
bb = MsgBox("You are about to update " & rs.RecordCount & " locations.  Confirm this is correct before proceeding since this cannot be undone", vbOKCancel, "Confirm Update")
 
 If bb = vbCancel Then
 Exit Sub
 Else
 
 con.Execute sSQL
 a = MsgBox("Success!", vbOKOnly)
 End If

End Sub

Open in new window

LVL 1
Tina KSystems SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

dsackerContract ERP Admin/ConsultantCommented:
I know you're using New Recordset, and I assume you have it references. I usually Dim rs As object and before line 20, would have this line of code:

Set rs = CreateObject("ADODB.Recordset")
Also, you won't get the record count reliably from rs.RecordCount.

However, you can reference it via rs(0).Value.

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
Tina KSystems SpecialistAuthor Commented:
Perfect solution - thanks so much!
Tina KSystems SpecialistAuthor Commented:
Here's the updated code with dsacker's excellent fixes:

Private Sub CommandButton1_Click()
Dim dte As String
Dim loc As String
Dim con As Object
Dim aa As String
Dim NoOfDataRows As String
Dim rs As Object

Set con = CreateObject("ADODB.Connection")
con.connectionstring = "Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=BTS;Initial Catalog=vmfg; password=m3talfab"
   
    dte = Worksheets("sheet1").Range("d1").Value
    loc = Worksheets("sheet1").Range("d3").Value
sSQL = "update location set count_date = '" & dte & "' where id like '" & loc & "%' "
con.Open

NoOfDataRows = "select count(*) from location where id like'" & loc & "%'"
Set rs = CreateObject("ADODB.Recordset")
rs.Open NoOfDataRows, con, adUseClient
rs.MoveLast

bb = MsgBox("You are about to update " & rs(0).Value & " locations.  Confirm this is correct before proceeding since this cannot be undone!", vbOKCancel, "Confirm Update")
 
 If bb = vbCancel Then
 Exit Sub
 Else
 
 con.Execute sSQL
 a = MsgBox("Success!", vbOKOnly)
 End If
End Sub

Open in new window

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 Excel

From novice to tech pro — start learning today.