Solved

Converting early binding to late binding (VBA ADODB)

Posted on 2012-04-11
7
1,602 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
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.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

810 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