编写VBA代码以从Excel连接到SQL Server 2005的最佳方法是什么?
excel文件的用户可能运行XP,Vista,Win7,并且我想尽可能避免安装驱动程序。
我的理解是XP使用MDAC,而Vista / Win7使用DAC。这是否意味着对MDAC 2.8的引用将无法在Vista计算机上运行,反之亦然?
如果我不添加引用并使用后期绑定,例如CreateObject(“ ADODB.Connection”)?
最佳答案
我使用MS ADO 2.0(在我的工作站上找到的最旧版本,将其添加为参考)完成了此操作。它可以在我尝试过的所有PC上运行,您只需要启用宏即可(这根本不是好消息)
Dim dbConnection As ADODB.Connection
Dim connStr As String
'Recordset variables
Dim rsData As ADODB.Recordset
Dim sql As String
connStr = "Provider=SQLOLEDB;" & _
"Data Source=MyServer\MyInstance;" & _
"Initial Catalog=MyDatabase;" & _
"Integrated Security=SSPI;" & _
"Application Name=MyExcelFile"
Set dbConnection = New ADODB.Connection
dbConnection.ConnectionString = connStr
dbConnection.Open
Set rsData = New ADODB.Recordset
rsData.Open "SELECT field FROM table", dbConnection
Dim field as String
Do While Not rsData.EOF
'this is where each row will be processed
field = rsData.Fields(0).Value
'do what's needed with field
rsData.MoveNext
Loop