locked
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
         '
         ' FOR THIS CODE TO WORK
         ' 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
            .ClearContents
            .CopyFromRecordset rs
        End With
         '            Tidy up
        rs.Close
        Set rs = Nothing
        Cn.Close
        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
      
    Handler:
      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
        gRST.Close
      End If
      
      gRST.Open qS, ssDb, adOpenStatic, adLockOptimistic
      If Not (gRST.EOF) Then
        rCnt = True
      Else
        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