Solved

Problem using excel vba to get data from sql

Posted on 2011-02-23
4
663 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
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.
0
 
LVL 10

Expert Comment

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

Author Closing Comment

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

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

777 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