Link to home
Start Free TrialLog in
Avatar of MBS1982
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:
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

Open in new window


Can anyone of you help me rewrite this?

Thanks in  advance

/Emil
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

I think this should do it:
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.Close

errhandler:


End Function

Open in new window

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:

Dim cmd as New ADODB.Command

Open in new window


becomes:
Dim cmd 'as New ADODB.Command
Set cmd = CreateObject("ADODB.Command")

Open in new window


and delete the reference from your project.
Avatar of MBS1982
MBS1982

ASKER

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.Close
errhandler:
        
        
    End Function

Open in new window


Any suggestions?
Try declaring your variables as Object, not Variant. If that doesn't work, please tell us what the error is.
Avatar of MBS1982

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
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MBS1982

ASKER

Thanks a lot! Great help :)