|
Sub 如何使用VBA实现多数据库查询()
Dim AdoConn As New ADODB.Connection
Dim strConn As String
Dim strSQL As String
'设置连接字符串
strConn = " Provider=SQLOLEDB.1;" & _
"Data Source=192.168.1.2\MSSQLSERVER;" & _
"Initial Catalog=UFDATA_2017;" & _
"User ID=sa;" & _
"Password=123456;"
";Extended Properties=""Excel 14.0;HDR=YES"";"
'设置SQL查询语句
strSQL = "Select SUM(数量) FROM [Sheet1$A1:A3] B Left Join" & _
"[DATABASE=" & ThisWorkbook.Path &
Application.PathSeparator & _
"UFDATA_2017.sz_zk_v_dhlb & " A on B.日期=A.日期 Group
By 日期"
'打开数据库链接
AdoConn.Open strConn
'执行查询,并将结果输出到当前表格B2
Sheets("Sheet1").Range("B2").CopyFromRecordset AdoConn.Execute
(strSQL)
'关闭数据库连接
AdoConn.Close
End Sub
------------------------------------------------------------------------------------------------
各位好,
我的SQL SERVER的IP是192.168.1.2,数据库用户名是sa,密码是123456,数据库文件是UFDATA_2017.sz_zk_v_dhlb
我想用EXCEL通过VBA ADO组件连接SQL SERVER的数据库,将数据库相应日期的数量,通过左外连接汇总到EXCEL工作表Sheet1$B2:B3单元格里,到底是哪里写错了请各位帮帮我!
|
|