SQL database RRS feed

  • Question

  • First of all i want to say im new to using VBA Excel 

    Now when that is said i need to Pull some data from a SQL database 

    i found this forum and tried using this code

    Sub ADOExcelSQLServer()
         ' Carl SQL Server Connection
         ' In VBE you need to go Tools References and check Microsoft Active X Data Objects 2.x library
        Dim Cn As ADODB.Connection
        Dim Server_Name As String
        Dim Database_Name As String
        Dim User_ID As String
        Dim Password As String
        Dim SQLStr As String
        Dim rs As ADODB.Recordset
        Set rs = New ADODB.Recordset
        Server_Name = "EXCEL-PC\EXCELDEVELOPER" ' Enter your server name here
        Database_Name = "AdventureWorksLT2012" ' Enter your database name here
        User_ID = "" ' enter your user ID here
        Password = "" ' Enter your password here
        SQLStr = "SELECT * FROM [SalesLT].[Customer]" ' Enter your SQL here
        Set Cn = New ADODB.Connection
        Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
        ";Uid=" & User_ID & ";Pwd=" & Password & ";"
        rs.Open SQLStr, Cn, adOpenStatic
         ' Dump to spreadsheet
        With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
            .CopyFromRecordset rs
        End With
         '            Tidy up
        Set rs = Nothing
        Set Cn = Nothing
    End Sub

    When i use this code i get a error message 

    Run-time error '-2147467259 (80004005):

    Automation error

    UnsPecified error

    also under Toiols-->Prefrences i have the following checked 

    • Visual Basic For APPlications
    • Microsoft Excel 16.0 Object Library
    • Microsoft Forms 2.0 Object Library
    • Microsoft Office 16.0 Object Library
    • Microsoft ActiveX Data Objects 2.8 Library
    • Microsoft ActiveX Data Objects Recordset 2.8 Library

    Any suggestions to what i need to change/ am doing wrong 

    Wednesday, May 16, 2018 8:55 PM

All replies

  • On which line of code does the error occur?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 17, 2018 1:42 PM
  • I'm currently migrating an SQLite DB to SQL Server.  I'm using:

    Microsoft ActiveX Data Objects 6.1 Library
    Microsoft Excel 14.0 Object Library
    Microsoft Office 14.0 Object Library

    Public ssDb As ADODB.Connection
    Public rs1 As ADODB.Recordset
    Public rs2 As ADODB.Recordset
    Function DbConnectSS() As Boolean
      Dim svrName As String
      Dim dbName As String
      Dim userId As String
      Dim password As String
      Dim cs As String
      On Error GoTo Handler
      Set ssDb = New ADODB.Connection
      Set rs1 = New ADODB.Recordset
      Set rs2 = New ADODB.Recordset
      svrName = "mypc\SQL1"
      dbName = ""
      userId = ""
      password = ""
      cs = ""
      cs = cs & "Driver={SQL Server};Server=" & svrName & ";Database=" & dbName
      cs = cs & ";Uid=" & userId & ";Pwd=" & password & ";"
      ssDb.Open cs
      DbConnectSS = True
      Exit Function
      Call MsgBox(Err.Number & ": " & Err.Description, vbOKOnly, "SQL Server DB Connect Error")
      DbConnectSS = False
    End Function

    I query using this:

    Public rCnt As Boolean
    Function SSQry(gRST As ADODB.Recordset, qS As String) As Boolean
      On Error GoTo ErrorHandler  ' Enable error-handling routine.
      If gRST.State = adStateOpen Then
      End If
      gRST.Open qS, ssDb, adOpenStatic, adLockOptimistic
      If Not (gRST.EOF) Then
        rCnt = True
        rCnt = False
      End If
      SSQry = True
      Exit Function     ' Exit to avoid handler.
    ErrorHandler:       ' Error-handling routine.
      Call MsgBox(Err.Number & ": " & Err.Description, vbOKOnly, "SQL Server Query Error")
      SSQry = False
    End Function

    Friday, May 18, 2018 12:58 PM