Connecting ADO connection to VB Control Button

[Steff]

Could anyone confirm for me whether the code snippet below is OK?

I have adapted it from a solution provided on this forum last year and
attached it to a VB button on a spreadsheet. It passes parameters into
a stored procedure on my SQL server that are stored in cells D1, D2 and
D3.

It works fine, but as I am a complete ADO novice, I am a bit confused
as to whether it is too simple to be true.

Everything I read about ADO says that you have to create and close a
connection to release system resources, yet this doesn't.

If somebody could confirm whether it is OK to use this code more widely
in a multi-user environment, without crashing my system, and if not,
how I would add and close the connection to this code, I would be
forever grateful.

Private Sub CommandButton1_Click()

Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
Dim BusArVar As String
Dim StartDateVar As Variant
Dim EndDateVar As Variant
Dim FinalRow As Integer

FinalRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
BusArVar = Sheets("Sheet1").Range("D1")
StartDateVar = Sheets("Sheet1").Range("D2")
EndDateVar = Sheets("Sheet1").Range("D3")


cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=My_Server;" _
& "Initial Catalog=My_DB; UID=My_ID;PWD=My_Password;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc

cmd.CommandText = "My_Stored_Procedure"
cmd.Parameters("@Owner").Value = BusArVar
cmd.Parameters("@StartDate").Value = StartDateVar
cmd.Parameters("@FinishDate").Value = EndDateVar
Set RS = cmd.Execute
Sheets("Sheet1").Range("B" & FinalRow + 1).CopyFromRecordset RS

End Sub

 

[Bob Phillips]

Looks okay, but you could tidy up with

Set cmd = Nothing
Set RS = Nothing

at the end

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Steff]

Thanks for the reassurance Bob,

I'll add your code for peace of mind.

Cheers

Steff