• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2926
  • Last Modified:

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
0
MBS1982
Asked:
MBS1982
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
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

0
 
Robert SchuttSoftware EngineerCommented:
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.
0
 
MBS1982Author Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rory ArchibaldCommented:
Try declaring your variables as Object, not Variant. If that doesn't work, please tell us what the error is.
0
 
MBS1982Author Commented:
Did not work with Object, it returns runtime error 3708 (parameter object is improperly defined. inconsistent or incomplete information was supplied)
0
 
Rory ArchibaldCommented:
Ah, missed a couple of constants:
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.Close

errhandler:


End Function

Open in new window

0
 
MBS1982Author Commented:
Thanks a lot! Great help :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now