Problem using excel vba to get data from sql

Posted on 2011-02-23
Last Modified: 2012-08-24
Been trying out some sample code to populate Excel spreadsheet with data from sql server database using vba. Spreadsheet works fine when tested against an Access database, but having problems when changing the connection details to sql server database - only difference between the access and sql versions are the sConnect string in the attached code.

Functionality should be that user enters a specific id into cell A4 of spreadsheet, then cell A5 is populated from the database by getting a description from the database that is associated with the id entered in cell A4 - so cell A5 has a formula of =GetDatabaseValue(A4) with GetDatabaseValue being a function in the vba code (code is attached).

Having two problems. First is that I get an error of "Supplied provider is different from the one already in use" from the SQLRead function. If I comment out the following block from the attached code, that particular error disappears :

    '   If connection object not sent, create from connection string
    cnSent = True
    If cn Is Nothing Then
        cnSent = False
        Set cn = New ADODB.Connection
        cn.Properties("Prompt") = adPromptComplete
        cn.Open sConnect, "", ""
    End If

However that then leads on to the second problem - I then get an error of "Object variable or With block variable not set" reported from the GetDatabaseValue function.

Would appreciate any assistance in resolving these problems or even with coming up with alternative vba code to achieve the same result - frustrating as the same vba code works fine against access, and the only change in the sql server version is changing sConnect to reflect SQL Server rather than Access .....
Function SQLRead(sSQL As String, sConnect As String, _
                 Optional iTimeOut As Integer, _
                 Optional bDisplayErrors As Boolean, _
                 Optional cn As ADODB.Connection) As String
'   SQLRead:     Returns a database result set as a string
'   Parameters:  sSQL           SQL Select statement
'                sConnect       Connection String
'                iTimeout       Milliseconds to wait for results before error
'                bDisplayErrors Set to False to surpress error displays
'                cn             Connection Object. If sent, sConnect is ignored
'                               Use this to speed up multiple reads
'   Example:     Cell(1,2) =  SQLExec(sSQL, sConnect)
    On Error GoTo ErrHandler
    SQLRead = Failure    'Assume Something went wrong
    Dim cnSent As Boolean
    Dim rs As ADODB.Recordset
    Dim s As String
    Dim lCols As Long
    Dim l As Long
'   If connection object not sent, create from connection string
    cnSent = True
    If cn Is Nothing Then
        cnSent = False
        Set cn = New ADODB.Connection
        cn.Properties("Prompt") = adPromptComplete
        cn.Open sConnect, "", ""
    End If
    Set rs = New ADODB.Recordset
'   Restrict how long the query can run before failing
    If iTimeOut > 0 Then
        cn.CommandTimeout = iTimeOut
    End If
'   Get the data from the database
    rs.Open sSQL, cn
'   Put results into a comma delimited string
    lCols = rs.Fields.Count
    If Not rs.EOF Then
        s = ""
        If Not rs.EOF Then
            For l = 0 To lCols - 1
                s = s & IIf(l > 0, ",", "") & """" & rs(l) & """"
            Next l
        End If
    End If
    '   Return results
    SQLRead = s
    If Err.Number <> 0 Or cn.Errors.Count <> 0 Then
        SQLRead = Failure
        If Err.Number <> 0 Then
            MsgBox "SQLRead - Error#" & Err.Number & vbCrLf & Err.Description, _
                vbCritical, "Error", Err.HelpFile, Err.HelpContext
            Dim errLoop As ADODB.Error
            For Each errLoop In cn.Errors
                MsgBox "SQLRead - Error number: " & errLoop.Number & vbCr & _
                    errLoop.Description, vbCritical, "Error", _
                    errLoop.HelpFile, errLoop.HelpContext
            Next errLoop
        End If
    End If
    On Error Resume Next
    If rs.State > 0 Then rs.Close   'Close the record set
'   Close the connection object if created in this routine
    If Not cnSent Then If cn.State > 0 Then cn.Close
    On Error GoTo 0
End Function

Public Function GetDatabaseValue(sID As String) As String
'   GetDatabaseValue:  Get DatabaseValue UDF
'   Parameters:    ID     Database ID
'   Example:       =GetDatabaseValue("A4")
    On Error GoTo ErrHandler
    Dim sSQL As String
    Dim sConnect As String
    sSQL = "SELECT  C.DescriptionValue  " & _
           "FROM    dbtablename C " & _
           "WHERE   C.IDVALUE = " & sID
    sConnect = "Provider=SQLOLEDB;Data Source=SQLSERVERNAME;Initial Catalog = DBNAME;Trusted_Connection=yes"
    GetDatabaseValue = Replace(SQLRead(sSQL, sConnect), """", "")
    If Err.Number <> 0 Then MsgBox _
        "GetDatabaseValue- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
    On Error GoTo 0
End Function

Open in new window

Question by:raymurphy
  • 3
LVL 10

Accepted Solution

cdebel earned 500 total points
ID: 34960807
What are your connections strings for both databases?  The problem is probably there.

At line 28, i would try to just put this: cn.Open sConnect

Also, it's normal that if you comment this part of code that you get an Object Variable or With block variable not set" since CN variable will not be created.
LVL 10

Expert Comment

ID: 34960853
Also important, at which line the program fail when you keep lines 23 to 29 uncommented?

Author Closing Comment

ID: 34961197
Thanks for this suggestion, cdebel ....

Changing cn.Open sConnect, "", "" to just cn.Open sConnect in itself didn't cure the problem.
But then I began to wonder if the cn.Properties("Prompt") = adPromptComplete line was actually needed - commenting out that line then solved the problem, and the spreadsheet was able to retrieve the data from the database as expected.

Got there in the end - thanks for your help.
LVL 10

Expert Comment

ID: 34961221
This line with adPromptComplete is to let the user enter his username and password if my memory is good.  That's why i asked to remove the empty username & password on the call of Open.

Glad you found out the problem.

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

756 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