vbaexcelconnection-stringsql-server-2014-express

Connection string for SQL Server 2014 Express (VBA)


I am trying figure out what needs to go in the connection string for SQL server via VBA.

This is the code I have right now,

Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=INSTANCE\SQLEXPRESS;" & _
              "Initial Catalog=MyDatabaseName;" & _
              "Integrated Security=SSPI;"

                ' Create the Connection and Recordset objects.
                Set conn = New ADODB.Connection
                Set rs = New ADODB.Recordset

                ' Open the connection and execute.
                    conn.Open sConnString


                      'Do my stuff here


                    If CBool(conn.State And adStateOpen) Then conn.Close
                Set conn = Nothing
                Set rs = Nothing

End Sub

Problem is I don't know what to put in the Connection string. My full File Path is this.

C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Staff_Manager.mdf

Can someone tell what needs to go with the,

"Provider"
 "Source"
 "Initial Catalog"

Thanks.


Solution

  • Please see this link.

    http://www.connectionstrings.com/

    Also, see this sample script, which works perfectly fine for me.

    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