Connection string for SQL Server 2014 Express (VBA)

Question

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.

See [Lesson 2: Specifying Connection Information](https://msdn.microsoft.com/en-us/library/ms166599.aspx) for parellel information. — , Dec 21 '15 at 07:07
Yeah, I am still very new to SQL Server and that link is basically what I am having the problem with. — , Dec 21 '15 at 07:28
The provider should be fine, the `Data Source` needs to be the server name and the `Initial Catalog` is the name of the actual database you want to connect to. — Rory, Dec 21 '15 at 08:18

score 17 · Accepted Answer · answered Dec 21 '15 at 19:52

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

I don't know what is my server name.... I installed SQL Server Express 2008 on my PC... Where I can find the server name... — Jing He, Nov 22 '17 at 08:53

score 0 · Answer 2 · edited Dec 02 '19 at 20:48

0

Server_Name = YOUR SERVER NAME or SERVER IP in double quotes for example "192.168.0.89,1433" in the case of SQL SERVER

The server name is the name that you put when you install it or try:

.\SQLEXPRESS

edited Dec 02 '19 at 20:48

mustaccio

18,234
16
48
57

answered Dec 02 '19 at 20:29

Cristian Günther

1
2

Connection string for SQL Server 2014 Express (VBA)

2 Answers2

Linked

Related