Solved

Problem using excel vba to get data from sql

Posted on 2011-02-23
4
655 Views
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
        rs.MoveFirst
        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
          
ErrHandler:
   
    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
        Else
            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), """", "")
    
ErrHandler:
   
    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

0
Comment
Question by:raymurphy
  • 3
4 Comments
 
LVL 10

Accepted Solution

by:
cdebel earned 500 total points
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
Also important, at which line the program fail when you keep lines 23 to 29 uncommented?
0
 

Author Closing Comment

by:raymurphy
Comment Utility
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.
0
 
LVL 10

Expert Comment

by:cdebel
Comment Utility
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now