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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL file checking. 11 46
Excel automatically jump to row based on date 5 39
Excel formula to calculate ID # 4 39
the UDF returns #Value when i open workbook. 19 42
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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

738 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