Converting early binding to late binding (VBA ADODB)
Hi,
I have a VBA function which calls an SQL Stored Procedure. For that I am currently using early binding, but would like to change to late binding to avoid reference problems on different computers.
The current script is:
Function SQL_DoThisThing(rng As Range, DateVal As Date, PeriodVal As Integer, AM As String) As Boolean Dim cmd As New ADODB.Command Dim conn As ADODB.Connection Dim prm As ADODB.Parameter Dim strConn As String Dim strSQL As String Dim ProcName As String Set RecSet = New ADODB.Recordset SQL_DoThisThing= False ProcName = "someStoredProcedureName" On Error GoTo errhandler strConn = "Provider=SQLOLEDB.1;" & "Data Source=" & sqlAdr & "; Initial Catalog=" & sqlDb & ";Trusted_connection=yes;" Set conn = New ADODB.Connection conn.Open strConn Set cmd = New ADODB.Command cmd.CommandTimeout = 0 cmd.CommandText = ProcName cmd.CommandType = adCmdStoredProc cmd.ActiveConnection = conn Set prm = cmd.CreateParameter("Date", adDate, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Date").Value = DateVal Set prm = cmd.CreateParameter("Period", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Period").Value = PeriodVal Set prm = cmd.CreateParameter("AM", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("AM").Value = AM 'Execute the Stored Procedure cmd.ActiveConnection = strConn Set spobj = cmd.Execute() rng.CopyFromRecordset spobj SQL_DoThisThing= True conn.Closeerrhandler: End Function
Function SQL_DoThisThing(rng As Range, DateVal As Date, PeriodVal As Integer, AM As String) As Boolean ' constants required from ADODB library Const adCmdStoredProc As Long = 4 Const adParamInput As Long = 1 Dim cmd As Object Dim conn As Object Dim prm As Object Dim strConn As String Dim strSQL As String Dim ProcName As String Set RecSet = CreateObject("ADODB.Recordset") SQL_DoThisThing = False ProcName = "someStoredProcedureName" On Error GoTo errhandler strConn = "Provider=SQLOLEDB.1;" & "Data Source=" & sqlAdr & "; Initial Catalog=" & sqlDb & ";Trusted_connection=yes;" Set conn = CreateObject("ADODB.Connection") conn.Open strConn Set cmd = CreateObject("ADODB.Command") cmd.CommandTimeout = 0 cmd.CommandText = ProcName cmd.CommandType = adCmdStoredProc cmd.ActiveConnection = conn Set prm = cmd.CreateParameter("Date", adDate, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Date").Value = DateVal Set prm = cmd.CreateParameter("Period", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Period").Value = PeriodVal Set prm = cmd.CreateParameter("AM", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("AM").Value = AM 'Execute the Stored Procedure cmd.ActiveConnection = strConn Set spobj = cmd.Execute() rng.CopyFromRecordset spobj SQL_DoThisThing = True conn.Closeerrhandler:End Function
Thanks a lot guys! It did help a lot and It makes perfect sense. However the code debugs on "cmd.Parameters.Append prm".
Function SQL_DoThisThing(rng As Range, resDate As Date, numDays As Integer, AM As String) As Boolean Dim cmd As Variant Dim conn As Variant Dim prm As Variant Set RecSet = CreateObject("ADODB.Recordset") Set conn = CreateObject("ADODB.Connection") Set cmd = CreateObject("ADODB.Command") Dim strConn As String Dim strSQL As String Dim ProcName As String Const adCmdStoredProc As Long = 4 Const adParamInput As Long = 1 SQL_DoThisThing= False ProcName = "someStoredProcedureName" On Error GoTo errhandler strConn = "Provider=SQLOLEDB.1;" & "Data Source=" & sqlAdr & "; Initial Catalog=" & sqlDb & ";Trusted_connection=yes;" conn.Open strConn cmd.CommandTimeout = 0 cmd.CommandText = ProcName cmd.CommandType = adCmdStoredProc cmd.ActiveConnection = conn Set prm = cmd.CreateParameter("DateStart", adDate, adParamInput, 20) cmd.Parameters.Append prm '<<<<------ debugs cmd.Parameters("DateStart").Value = resDate Set prm = cmd.CreateParameter("NumberOfdays", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("NumberOfdays").Value = numDays Set prm = cmd.CreateParameter("AggregationMethod", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("AggregationMethod").Value = AM cmd.ActiveConnection = strConn Set spobj = cmd.Execute() rng.CopyFromRecordset spobj SQL_DoThisThing = True conn.Closeerrhandler: End Function
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Function SQL_DoThisThing(rng As Range, DateVal As Date, PeriodVal As Integer, AM As String) As Boolean ' constants required from ADODB library Const adCmdStoredProc As Long = 4 Const adParamInput As Long = 1 Const adDate As Long = 7 Const adVarChar As Long = 200 Dim cmd As Object Dim conn As Object Dim prm As Object Dim strConn As String Dim strSQL As String Dim ProcName As String Set RecSet = CreateObject("ADODB.Recordset") SQL_DoThisThing = False ProcName = "someStoredProcedureName" On Error GoTo errhandler strConn = "Provider=SQLOLEDB.1;" & "Data Source=" & sqlAdr & "; Initial Catalog=" & sqlDb & ";Trusted_connection=yes;" Set conn = CreateObject("ADODB.Connection") conn.Open strConn Set cmd = CreateObject("ADODB.Command") cmd.CommandTimeout = 0 cmd.CommandText = ProcName cmd.CommandType = adCmdStoredProc cmd.ActiveConnection = conn Set prm = cmd.CreateParameter("Date", adDate, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Date").Value = DateVal Set prm = cmd.CreateParameter("Period", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("Period").Value = PeriodVal Set prm = cmd.CreateParameter("AM", adVarChar, adParamInput, 20) cmd.Parameters.Append prm cmd.Parameters("AM").Value = AM 'Execute the Stored Procedure cmd.ActiveConnection = strConn Set spobj = cmd.Execute() rng.CopyFromRecordset spobj SQL_DoThisThing = True conn.Closeerrhandler:End Function
There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.
Open in new window