4

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.

Improve this question
3
  • See Lesson 2: Specifying Connection Information for parellel information. Commented Dec 21, 2015 at 7:07
  • Yeah, I am still very new to SQL Server and that link is basically what I am having the problem with. Commented Dec 21, 2015 at 7:28
  • 1
    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. Commented Dec 21, 2015 at 8:18

2 Answers 2

Reset to default
17

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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I got it working, I was did not name the server correctly.
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...
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.