Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Converting early binding to late binding (VBA ADODB)

Posted on 2012-04-11
7
Medium Priority
?
2,268 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

885 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