none
Excel macro vba error while fetching data from SQL server RRS feed

  • Question

  • Hello All,

    I've written a macro that takes excel value queries the sql server db(SQL server 2000) and puts data again on the same spreadsheet. For some reason, I'm getting below error message: Here is my code.

    Sub ConnectSqlServer()
    
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim newrow As String
    
    
    'MODIFIED: create the search string for the IN-Statement
    newrow = "("
    For i = 1 To Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row
        newrow = newrow & "'" & Trim(Worksheets("Sheet1").Cells(i, "A").Value) & "',"
    Next i
    newrow = Left(newrow, Len(newrow) - 1)
    newrow = newrow & ")"
    
    ' Create the connection string.
     sConnString = "Provider=SQLOLEDB;Data Source=0.0.0.0;" & _
                      "Initial Catalog=asset;" & _
                      "User ID=test;Password=test123;"
                      
    
                      
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    ' Open the connection and execute.
    conn.Open sConnString
    'MODIFIED: altered the SQL statement to use the search string with IN
    Debug.Print "SELECT [customer] FROM [asset].[dbo].`[job_tab]` where [jobnum2] IN " & Trim(newrow) & "';"; –; ""
    
    
    Set rs = conn.Execute("SELECT [customer] FROM [asset].[dbo].[job_tab] " & _
                           "where [jobnum2] IN '" & Trim(newrow) & "'")
    
    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(1).Range("B1").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
    
    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
    End Sub
    
    
    
    
    
    
    
    

    Wednesday, April 6, 2016 3:02 PM

Answers

All replies