VBA Excel到SqlServer [英] VBA Excel To SqlServer

问题描述

编写VBA代码以从Excel连接到SQL Server 2005的最佳方法是什么?

What is the best way to write VBA code to connect to SQL Server 2005 from Excel?

excel文件的用户可能运行XP，Vista，Win7，我想尽可能避免安装驱动程序.

The users of the excel file might run XP, Vista, Win7 and I want to prevent driver installation as much as possible.

我的理解是XP使用MDAC，而Vista/Win7使用DAC.这是否意味着对MDAC 2.8的引用将无法在Vista计算机上运行，​​反之亦然?

My understanding is that XP uses MDAC while Vista/Win7 uses DAC. Does that mean that a reference to MDAC 2.8 will not work on a Vista machine and the other way around?

如果我不添加引用并使用后期绑定(例如， CreateObject("ADODB.Connection")?

Will my VBA code work on both if I don't add a reference and use late binding, e.g. CreateObject("ADODB.Connection")?

推荐答案

我已经使用MS ADO 2.0(在我的工作站上找到的最旧版本，将其添加为参考)完成了此操作.它可以在我尝试过的所有PC上正常工作，您只需要启用宏(这根本不是好消息)

I've done this using MS ADO 2.0 (the oldest version found on my workstation, added it as a reference). It's working on all the PC's I've tried it, you only have to enable macros (which wasn't good news at all)

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

这篇关于VBA Excel到SqlServer的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！