Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Problem using excel vba to get data from sql

Posted on 2011-02-23
Medium Priority
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

Christian de Bellefeuille earned 2000 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

by:Christian de Bellefeuille
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

by:Christian de Bellefeuille
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
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 …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

721 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