SQL Server 2014 Express (VBA) 的连接字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34389720/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Connection string for SQL Server 2014 Express (VBA)
提问by
I am trying figure out what needs to go in the connection string for SQL server via VBA.
我正在尝试通过 VBA 找出 SQL 服务器的连接字符串中需要添加的内容。
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.
谢谢。
回答by
Please see this link.
请看这个链接。
http://www.connectionstrings.com/
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
回答by Cristian Günther
Server_Name = YOUR SERVER NAME or SERVER IP in double quotes for example "192.168.0.89,1433" in the case of SQL SERVER
Server_Name = YOUR SERVER NAME 或双引号中的 SERVER IP 例如“192.168.0.89,1433”在 SQL SERVER 的情况下
The server name is the name that you put when you install it or try:
服务器名称是您在安装或尝试时输入的名称：
.\SQLEXPRESS
.\SQLEXPRESS
