Solved

Converting early binding to late binding (VBA ADODB)

Posted on 2012-04-11
7
1,696 Views
Last Modified: 2012-04-11
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
Comment
Question by:MBS1982
  • 3
  • 3
7 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37831469
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
 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37831475
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
 

Author Comment

by:MBS1982
ID: 37831529
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37831586
Try declaring your variables as Object, not Variant. If that doesn't work, please tell us what the error is.
0
 

Author Comment

by:MBS1982
ID: 37831595
Did not work with Object, it returns runtime error 3708 (parameter object is improperly defined. inconsistent or incomplete information was supplied)
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37831605
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
 

Author Closing Comment

by:MBS1982
ID: 37831609
Thanks a lot! Great help :)
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question