MBS1982
asked on
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:
Can anyone of you help me rewrite this?
Thanks in  advance
/Emil
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.Close
errhandler:
End Function
Can anyone of you help me rewrite this?
Thanks in  advance
/Emil
instead of New ADODB.xxx, use CreateObject("ADODB.xxx"), at all Dim var AS xxx, remove (or comment out) the "AS xxx" part.
Note the combination:
becomes:
and delete the reference from your project.
Note the combination:
Dim cmd as New ADODB.Command
becomes:
Dim cmd 'as New ADODB.Command
Set cmd = CreateObject("ADODB.Command")
and delete the reference from your project.
ASKER
Thanks a lot guys! It did help a lot and It makes perfect sense. However the code debugs on "cmd.Parameters.Append prm".
Any suggestions?
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.Close
errhandler:
End Function
Any suggestions?
Try declaring your variables as Object, not Variant. If that doesn't work, please tell us what the error is.
ASKER
Did not work with Object, it returns runtime error 3708 (parameter object is improperly defined. inconsistent or incomplete information was supplied)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot! Great help :)
Open in new window