[分享]利用ADO建立EXCEL与SQL SERVER、ACCESS的联系，可以导入、导出EXCEL数据

henrry

首先在VBE中选中菜单“工具”－“引用”，在弹出的对话框中，在“Microsoft ActiveX Data Objects 2.1 library”前打勾选中，也可以用2.1以上的版本。这样就建立了ADO的引用。 Sub insertData()
Dim I%, strTemp$, strTemp2$, RowNum%
Dim wkSheet As Worksheet

I = MsgBox("确认要导入数据吗？", vbYesNo)
If I = 7 Then
Exit Sub
End If
Conn.Open "provider=sqloledb.1;persist security info=false;user id=用户名;pwd=口令;data source=192.168.2.2;initial catalog=首选数据库" Application.ScreenUpdating = False
Set wkSheet = Worksheets("Check")
wkSheet.Activate
I = 2
Do While wkSheet.Cells(I, 1).Value <> ""
I = I + 1
Loop
I = I - 1
RowNum = I '开始倒数据 '直接插入数据表
Conn.BeginTrans
For I = 2 To RowNum
If wkSheet.Cells(I, 4).Value <> "" Then
strTemp = "insert into tprice (colno,productname,spec,editdate,unit,price,provider) "
strTemp = strTemp & " values('" & Trim((wkSheet.Cells(I, 7).Value)) & "','"
strTemp = strTemp & Replace(wkSheet.Cells(I, 1).Value, "'", "''") & "','" & Replace(wkSheet.Cells(I, 2).Value, "'", "''") & "','"
strTemp = strTemp & Format(Now, "yyyy-mm-dd hh:mm:ss") & "','" & Replace(wkSheet.Cells(I, 3).Value, "'", "''")
strTemp = strTemp & "'," & wkSheet.Cells(I, 4).Value & ",'" & wkSheet.Cells(I, 5).Value & "')"
Conn.Execute strTemp
End If
Next
Conn.CommitTrans

ThisWorkbook.Save Conn.Execute "update tprice set tprice.colname=colno.colname from tprice,colno where tprice.colno=colno.colno and tprice.colname is null"
Conn.Close
Set Conn = Nothing
Set wkSheet = Nothing
Application.ScreenUpdating = True
MsgBox "导入完毕！"
End Sub

cdrr

henrry 太棒了 纵里录他千百度，蓦然回首，这个东东却在，henrry贴子一楼处！
真是感谢！ 另外在请教，我的服务器有一个（ORACLE）数据库，其中里面有一个叫SSZ01的表， 请问能在工用站上远程调用这个SZZ01这个表，并且把其中符合要求的数据倒入到EXCEL中吗？ 请henrry给个如上的具体实例好吗？ 还有再问，ADO是个什么概念我一直搞不明白，是个独立软件？还是本身在OFFICE中就自带有的？你上面用的“Microsoft ActiveX Data Objects 2.1 library”是不是就是ADO？不需要另外安装？我在有的地方看到ADO好像还有版本号，这又从哪里查到的？

[此贴子已经被作者于2004-3-12 13:09:03编辑过]

henrry

Microsoft® ActiveX® Data Objects (ADO) 使您的客户端应用程序能够通过 OLE DB 提供者访问和操作在数据库服务器中的数据。
ADO 支持用于建立基于客户端/服务器和 Web 的应用程序的主要功能。其主要优点是易于使用、高速度、低内存支出和占用磁盘空间较少。ADO 同时具有远程数据服务 (RDS) 功能，通过 RDS 可以在一次往返过程中实现将数据从服务器移动到客户端应用程序或 Web 页、在客户端对数据进行处理然后将更新结果返回服务器的操作。

cdrr

以下是引用henrry在2004-3-12 13:31:00的发言：
Microsoft® ActiveX® Data Objects (ADO) 使您的客户端应用程序能够通过 OLE DB 提供者访问和操作在数据库服务器中的数据。
ADO 支持用于建立基于客户端/服务器和 Web 的应用程序的主要功能。其主要优点是易于使用、高速度、低内存支出和占用磁盘空间较少。ADO 同时具有远程数据服务 (RDS) 功能，通过 RDS 可以在一次往返过程中实现将数据从服务器移动到客户端应用程序或 Web 页、在客户端对数据进行处理然后将更新结果返回服务器的操作。

还是不明白，能请你针对在二楼的问题讲解一下吗？

[此贴子已经被作者于2004-3-12 13:40:33编辑过]

henrry

Dim oconn As New ADODB.Connection
Dim Rs As New ADODB.Recordset
StrConn = "Provider=msdaora;Data Source=MyOracleDB; User Id=myUsername; Password=myPassword;"
oconn.Open StrConn Rs.Open "select * from 表", oconn, adOpenForwardOnly Rs.Close
Set Rs = Nothing
Set oconn = Nothing

henrry

在VBE中选中菜单“工具”－“引用”，在弹出的对话框中，有 “Microsoft ActiveX Data Objects 2.0 library” “Microsoft ActiveX Data Objects 2.1 library” “Microsoft ActiveX Data Objects 2.5 library” “Microsoft ActiveX Data Objects 2.6 library” “Microsoft ActiveX Data Objects 2.7 library” “Microsoft ActiveX Data Objects 2.8 library” 等版本。 在微软的网站可以单独下载安装，安装OFFICE后就会有2.0或2.1的版本，它本身是个部件，它提供一个访问数据库的通道，用它可以建立对数据库的链接，可以通过这个链接执行SQL语句，对数据表进行插入、删除等各种操作。

cdrr

非常感谢,“Microsoft ActiveX Data Objects 2.8 library等等,是不是就是ADO? 呵,我又没安装,我怎么我的机器上从2.0到2.8都有? StrConn = "Provider=msdaora;Data Source=MyOracleDB; User Id=myUsername; Password=myPassword;"之中 Provider=msdaora是什么意思? MyOracleDB是不是就是我要访问的数据库如换成我的数据库(swdb)?

[此贴子已经被作者于2004-3-12 14:16:08编辑过]

henrry

Data Source=192.168.1.2或机器名 Provider=msdaora　　是对ORACELR连接 Provider=SQLOLEDB　是对SQL　SERVER连接 ADO是对　Microsoft ActiveX Data Objects的简称

henrry

Provider 和 DefaultDatabase 属性范例
该范例通过打开三个使用不同提供者的 Connection 对象演示 Provider 属性。还使用 DefaultDatabase 属性设置 Microsoft ODBC 提供者的默认数据库。 Public Sub ProviderX() Dim cnn1 As ADODB.Connection
Dim cnn2 As ADODB.Connection
Dim cnn3 As ADODB.Connection ' 使用 Microsoft ODBC 提供者打开连接。
Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "driver={SQL Server};" & _
"server=bigsmile;uid=sa;pwd=pwd"
cnn1.Open strCnn
cnn1.DefaultDatabase = "pubs"

' 显示提供者。
MsgBox "Cnn1 provider: " & cnn1.Provider ' 使用 Microsoft Jet 提供者打开连接。
Set cnn2 = New ADODB.Connection
cnn2.Provider = "Microsoft.Jet.OLEDB.3.51"
cnn2.Open "C:\Samples\northwind.mdb", "admin", "" ' 显示提供者。
MsgBox "Cnn2 provider: " & cnn2.Provider ' 使用 Microsoft SQL 服务器提供者打开连接。
Set cnn3 = New ADODB.Connection
cnn3.Provider = "sqloledb"
cnn3.Open "Data Source=srv;Initial Catalog=pubs;", "sa", "" ' 显示提供者。
MsgBox "Cnn3 provider: " & cnn3.Provider cnn1.Close
cnn2.Close
cnn3.Close End Sub

henrry

ADO 编程模型详细资料
以下元素是 ADO 编程模型中的关键部分： 连接
命令
参数
记录集
字段
错误
属性
集合
事件
连接 通过“连接”可从应用程序访问数据源，连接是交换数据所必需的环境。通过如 Microsoft® Internet Information Server 作为媒介，应用程序可直接（有时称为双层系统）或间接（有时称为三层系统）访问数据源。 对象模型使用 Connection 对象使连接概念得以具体化。 “事务”用于界定在连接过程中发生的一系列数据访问操作的开始和结束。ADO 可明确事务中的操作造成的对数据源的更改或者成功发生，或者根本没有发生。 如果取消事务或它的一个操作失败，则最终的结果将仿佛是事务中的操作均未发生，数据源将会保持事务开始以前的状态。 对象模型无法清楚地体现出事务的概念，而是用一组 Connection 对象方法来表示。 ADO 访问来自 OLE DB 提供者的数据和服务。Connection 对象用于指定专门的提供者和任意参数。例如，可对远程数据服务 (RDS) 进行显式调用，或通过“Microsoft OLE DB Remoting Provider”进行隐式调用。（请参阅 RDS 教程通过“MS Remote Provider”调用 RDS 第二步的范例） 命令 通过已建立的连接发出的“命令”可以某种方式来操作数据源。一般情况下，命令可以在数据源中添加、删除或更新数据，或者在表中以行的格式检索数据。 对象模型用 Command 对象来体现命令概念。Command 对象使 ADO 能够优化对命令的执行。 参数 通常，命令需要的变量部分即“参数”可以在命令发布之前进行更改。例如，可重复发出相同的数据检索命令，但每一次均可更改指定的检索信息。 参数对执行其行为类似函数的命令非常有用，这样就可知道命令是做什么的，但不必知道它如何工作。例如，可发出一项银行过户命令，从一方借出贷给另一方。可将要过户的款额设置为参数。 对象模型用 Parameter 对象来体现参数概念。 记录集 如果命令是在表中按信息行返回数据的查询（行返回查询），则这些行将会存储在本地。 对象模型将该存储体现为 Recordset 对象。但是，不存在仅代表单独一个 Recordset 行的对象。 记录集是在行中检查和修改数据最主要的方法。Recordset 对象用于： 指定可以检查的行。
移动行。
指定移动行的顺序。
添加、更改或删除行。
通过更改行更新数据源。
管理 Recordset 的总体状态。
字段 一个记录集行包含一个或多个“字段”。如果将记录集看作二维网格，字段将排列构成“列”。每一字段（列）都分别包含有名称、数据类型和值的属性，正是在该值中包含了来自数据源的真实数据。 对象模型以 Field 对象体现字段。 要修改数据源中的数据，可在记录集行中修改 Field 对象的值，对记录集的更改最终被传送给数据源。作为选项，Connection 对象的事务管理方法能够可靠地保证更改要么全部成功，要么全部失败。 错误 错误随时可在应用程序中发生，通常是由于无法建立连接、执行命令或对某些状态（例如，试图使用没有初始化的记录集）的对象进行操作。 对象模型以 Error 对象体现错误。 任意给定的错误都会产生一个或多个 Error 对象，随后产生的错误将会放弃先前的 Error 对象组。 属性 每个 ADO 对象都有一组唯一的“属性”来描述或控制对象的行为。 属性有两种类型：内置和动态。内置属性是 ADO 对象的一部分并且随时可用。动态属性则由特别的数据提供者添加到 ADO 对象的属性集合中，仅在提供者被使用时才能存在。 对象模型以 Property 对象体现属性。 集合 ADO 提供“集合”，这是一种可方便地包含其他特殊类型对象的对象类型。使用集合方法可按名称（文本字符串）或序号（整型数）对集合中的对象进行检索。 ADO 提供四种类型的集合： Connection 对象具有 Errors 集合，包含为响应与数据源有关的单一错误而创建的所有 Error 对象。
Command 对象具有 Parameters 集合，包含应用于 Command 对象的所有 Parameter 对象。
Recordset 对象具有 Fields 集合，包含所有定义 Recordset 对象列的 Field 对象。
另外，Connection、Command、Recordset 和 Field 对象都具有 Properties 集合。它包含所有属于各个包含对象的 Property 对象。
ADO 对象拥有可在其上使用的诸如“整型”、“字符型”或“布尔型”这样的普通数据类型来设置或检索值的属性。然而，有必要将某些属性看成是数据类型“COLLECTION OBJECT”的返回值。相应的，集合对象具有存储和检索适合该集合的其他对象的方法。 例如，可认为 Recordset 对象具有能够返回集合对象的 Properties 属性。该集合对象具有存储和检索描述 Recordset 性质的 Property 对象的方法。 事件 “事件”是对将要发生或已经发生的某些操作的通知。一般情况下，可用事件高效地编写包含几个异步任务的应用程序。 对象模型无法显式体现事件，只能在调用事件处理程序例程时表现出来。 在操作开始之前调用的事件处理程序便于对操作参数进行检查或修改，然后取消或允许操作完成。 操作完成后调用的事件处理程序在异步操作完成后进行通知。多个操作经过增强可以有选择地异步执行。例如，用于启动异步 Recordset.Open 操作的应用程序将在操作结束时得到执行完成事件的通知。