excel - 总是使用 VBA 获得“连接失败”连接 sqlserver
问题描述
我正在尝试从VBA程序连接sqlserver,我参考以下代码来实现它,但我遇到了问题:连接失败。任何帮助。先感谢您。
代码:
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
解决方案
测试这个。
Sub cn()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim i As Integer
con.ConnectionString = "Provider=SQLOLEDB.1;" _
& "Server=(local);" _
& "Database=AdventureWorksLT2012;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;"
con.Open
Set cmd.ActiveConnection = con
cmd.CommandText = "SELECT * FROM [SalesLT].[Customer]"
Set rst = cmd.Execute
Range("A1").CopyFromRecordset rst
con.Close
Set con = Nothing
End Sub
推荐阅读
- mysql - Django 不提交对 MySQL 数据库的更改
- ssl - 通过 cPanel 安装 SSL 时,“...IP 地址上已经有一个网站 *...”错误消息
- c# - .net IIS Classic 应用程序池将字节注入我的图像
- django - 使用单选按钮创建 Django 表单问卷
- php - angularTS到PHP文件上传“文件为空”
- autocomplete - 如何在 VSCode 中禁用自动关闭 JSX 标签?
- python - 从 beautifulsoup 中的列表中选择链接
- laravel - Laravel 5.6 域和子域文件存储
- angular - 遵循 Angular 风格指南,跨模块注入服务
- python - 使用 datetime 在 Python 中将 Int 值转换为填充小时