使用 ADO 操作 Access 数据库

[amnesiac]

注：本文改写自微软知识库文章，待找到源网址后补上。

在脚本中操作 Office 文档时通常需要使用它们专用的对象模型，例如对于 Word 文档，一般需要通过 Word 对象模型进行操作。这里不打算介绍 Access 对象模型，那么如何从 Access 数据库读取数据呢？这里介绍使用 ADO 方法。

实际上如果只需对 Access 数据库进行基本操作使用 ADO 已经足够了，同时具有下面几点好处：

• ADO 相对于 Access 对象模型简单多了，容易学习且应用广泛。
• ADO 在进程内执行，所以速度快。
• 无需安装 Access 即可操作 Access 数据库。

在本文中将介绍 ADO 的一些基础，尤其是如何使用 ADO 操作 Access 数据库：连接到数据库、获取信息、添加新记录、修改现有记录、添加和删除表以及其他能想到的内容。
这里假定您已经有了一个数据库文件 C:\Scripts\Inventory.mdb，且里面包含 GeneralProperties 表，而表中含有 ComputerName、Department、OperatingSystem 和 Owner 这几个字段。

从示例开始
现在首先看看从 Access 数据库中读取数据的脚本，这个脚本首先连接到 C:\Scripts\Inventory.mdb 文件，从 GeneralProperties 表中获取信息并返回表中每条记录里 ComputerName 字段的值：

Code: Select all

adOpenStatic := 3
adLockOptimistic := 3

objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")

objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\scripts\inventory.mdb")
objRecordSet.Open("SELECT * FROM GeneralProperties" , objConnection, adOpenStatic, adLockOptimistic)
objRecordSet.MoveFirst
while !objRecordSet.EOF
{
  MsgBox, % objRecordSet.Fields.Item["ComputerName"].Value
  objRecordSet.MoveNext
}

可以看到这段代码很短，实际上在 ADO 的脚本中这样的代码类似于模板，在进行其他操作时大部分都不需要改变。下面对这段代码进行详细的解释。

游标类型、锁定类型

Code: Select all

adOpenStatic := 3
adLockOptimistic := 3

首先定义两个常量——adOpenStatic 和 adLockOptimistic，设置它们的值为 3。adOpenStatic 用来设置记录集的游标类型，目前只需要知道它决定了我们可以如何在记录集中移动以及记录集是否响应数据库的实时更新（即假设当您从数据库中获取信息时别人添加新的记录到数据库中，那个记录是否动态显示到您的记录集中或者需要重新查询才能获取更新的信息）。adOpenStatic 游标能让您在记录集中前后移动，但不会自动获取数据库中的更新。这样有助于减少处理时间和缓解网络通信，因为一旦获取了记录集脚本就不需要持续监视数据库的更新。
下表中列出了游标类型常量、相应的值及说明：

Code: Select all

仅向前游标 adOpenForwardOnly 0 仅允许在记录集中向前移动。无法查找个别记录、无法返回记录集中的记录数，且无法查看到记录集中所有的变化。 
键集游标 adOpenKeyset 1 允许在记录集中前后移动、支持使用 Find 查找记录、可以返回记录数。能查看到现有记录的动态变化，但无法看到新添加的记录。 
动态游标 adOpenDynamic 2 允许在记录集中前后移动、支持使用 Find 查找记录、可以返回记录数，并且能查看到记录集中所有的变化。 
静态游标 adOpenStatic 3 允许在记录集中前后移动、支持使用 Find 查找记录、可以返回记录数，但无法查看到记录集中所有的变化。当打开客户端记录集时，只允许使用这种游标类型。 

注：并非所有的提供者都支持记录集的所有方法和属性。对于个人文件或其他不需要关心数据更新的情况，一般使用静态游标。
另一个常量 adLockOptimistic 用来设置记录的锁定类型。锁定类型决定了数据库是否及如何锁定当前查看的记录，以及提供修改这个记录的独占权利。下表中列出了锁定类型常量、相应的值及说明：

Code: Select all

Read Only adLockReadOnly 1 不锁定记录集来释放系统资源，不过这也导致记录集是只读的。 
Pessimistic adLockPessimistic 2 从开始编辑时锁定记录，一直到调用 Update 方法后解锁。 
Optimistic adLockOptimistic 3 仅在调用 Update 方法时临时锁定记录。 
Batch adLockOptimisticBatch 4 用于批次更新。 

创建对象并查询
定义了常量后创建两个对象：Connection 对象和 Recordset 对象。它们的名称指明各自的用途：Connection 对象用于管理和维护到数据库的连接，而 Recordset 对象保存查询返回的数据。

Code: Select all

objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")

接着连接到 C:\Scripts\Inventory.mdb 数据库：

Code: Select all

objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\scripts\inventory.mdb")

这里调用 Connection 对象的 Open 方法并向它传递了一个参数，这个参数被称为连接字符串，其中包含了提供者的名称（Microsoft.Jet.OLEDB.4.0）和数据源（这里是要连接的数据库的路径）。
现在连接到了 inventory.mdb 数据库，接着进行 SQL 查询：从 GeneralProperties 中选择所有字段（这是最基本的查询）。

Code: Select all

SELECT * FROM GeneralProperties

需要把这个查询提供给记录集对象的 Open 方法：

Code: Select all

objRecordSet.Open("SELECT * FROM GeneralProperties" , objConnection, adOpenStatic, adLockOptimistic)

这里的四个参数分别表示：

• SQL 查询（除了 SELECT 查询外，还可以使用 Update 查询、Insert Into 查询和其他查询）。
• 数据库连接的对象引用（objConnection）。
• 游标类型（adOpenStatic 常量表示静态游标）。
• 锁定类型（adLockOptimistic 常量表示 optimistic）。

执行后查询返回的数据会保存在记录集对象中。

排序
默认情况下，记录集中记录的顺序是取决于它们被添加到数据库时的顺序，而不会在返回时对它们进行排序。如果需要进行排序，只需添加 ORDER BY 子句并指定用来排序的字段和排序的类型（升序为 ASC，降序为 DESC）。例如下面这个查询会对返回的结果根据 Manufacturer 字段进行升序（从 A 到 Z）排列：

Code: Select all

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC

而下面这个则以 Manufacturer 为主要关键字、 ComputerName 为次要关键字进行排序：

Code: Select all

SELECT * FROM GeneralProperties ORDER BY Manufacturer ASC, ComputerName ASC

在记录集中移动
为了遍历记录集中的所有记录，需要进行两项操作：调用 MoveFirst 方法和通过循环遍历记录集中的每个记录。

Code: Select all

objRecordSet.MoveFirst

使用 MoveFirst 方法把游标移动到记录集的开始处，实际上并不是一定要这么做，我们一般可以相信游标会自动定位到记录集的开始处，不过宁愿稳妥免致后悔。另外，定位游标到记录集的开始处简单说来是让游标指向第一个记录，这样可以确保在遍历时确保是从第一个记录开始。如果由于特殊情况需要访问最后一个记录，可以使用 MoveLast 方法。当然，还可以用 Find 方法查找记录集中的任何记录。
现在使用循环遍历记录集中的每个记录：

Code: Select all

while !objRecordSet.EOF
{
  MsgBox, % objRecordSet.Fields.Item("ComputerName").Value
  objRecordSet.MoveNext
}

这里是新手容易出问题的地方，有几点需要注意：

• 这里使用 While 循环。 尽管可以使用 Loop-Until 循环，但由于它是首先执行一次循环体后才测试循环条件，所以当记录集中不包含记录时会产生错误（如果在前面增加一个 If 语句对 RecordCount 属性进行判断，显然直接用 While 循环更简单）。

  Code: Select all

  If (objRecordset.RecordCount <> 0)
  {
    Loop
    {
      MsgBox, % objRecordSet.Fields.Item("ComputerName").Value
      objRecordSet.MoveNext
    }Until objRecordSet.EOF
  }

• 循环的条件是 !objRecordSet.EOF 为真，即 objRecordSet.EOF 为假。EOF 是“End Of File”的简写形式，这里表示记录集的末尾（在最后一个记录的后面）。当到达记录集的末尾时，则退出循环。
• 由于 While 循环不会自动遍历记录集中的每个记录，所以必须在循环体中包含如何移动到下一个记录的代码。这就是 MoveNext 方法的用途：

  Code: Select all

  objRecordSet.MoveNext

  如果没有这行代码，那么游标会一直停留在第一个记录上，而 While 循环也持续对第一个记录操作，并且变成了无限循环。所以使用 MoveNext 方法是很重要的，这也是获取记录集中每个记录的唯一方法。

引用记录集中的字段
最后，需要理解如何引用数据库中的字段：

Code: Select all

MsgBox, % objRecordSet.Fields.Item["ComputerName"].Value

objRecordSet.Fields.Item["ComputerName"].Value 表示 objRecordSet 记录集中当前字段集合中的“ComputerName”字段名对应的项。这里需注意，在 VBScript 中字段名括在小括号中，但在这里是方括号。
还有其他引用方式，测试这三种形式在引用记录和添加新记录时是否都有效
objRecordSet.Fields["ComputerName"].Value
objRecordSet["ComputerName"] := "atl-ws-99"

添加新记录
下面是添加新记录到数据库的脚本：

Code: Select all

adOpenStatic := 3
adLockOptimistic := 3

objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")

objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\scripts\inventory.mdb")

objRecordSet.Open("SELECT * FROM GeneralProperties" , objConnection, adOpenStatic, adLockOptimistic)

objRecordSet.AddNew
objRecordSet["ComputerName"] := "atl-ws-99"
objRecordSet["Department"] := "Human Resources"
objRecordSet["OperatingSystem"] := "Microsoft Windows XP Professional"
objRecordSet["Owner"] := "Ken Myer"
objRecordSet.Update

objRecordSet.Close
objConnection.Close

在这段脚本的开始处，首先连接到 inventory.mdb 数据库并进行 SQL 查询获取 GeneralProperties 表中的所有记录。接着就可以添加新记录到刚才返回的记录集了：

Code: Select all

objRecordSet.AddNew
objRecordSet["ComputerName"] := "atl-ws-99"
objRecordSet["Department"] := "Human Resources"
objRecordSet["OperatingSystem"] := "Microsoft Windows XP Professional"
objRecordSet["Owner"] := "Ken Myer"
objRecordSet.Update

首先调用 AddNew 方法，这样建立了可操作的空模板，不过并没有添加任何东西到数据库（这时我们做的所有操作只发生在内存）。接下来的多行代码中我们为新记录的每个字段赋值（显然，这些字段都是 GeneralProperties 表中的字段）。例如下面这行把 atl-ws-99 分配给 ComputerName 字段：

Code: Select all

objRecordSet["ComputerName"] := "atl-ws-99"

注意这里把值（atl-ws-99）放在双引号中，因为 ComputerName 字段是文本类型，所以接收字符串值。对于数值类型或布尔类型的字段，为它们赋的值则不需要放在双引号中，例如：

Code: Select all

objRecordSet["IsLaptop"] := False
objRecordSet["NumberOfPrcoessors"] := 2

最后调用 Update 方法，这时才真正把新记录写入数据库：

Code: Select all

objRecordSet.Update

不要忘记这行代码，否则内存中的记录集已经包含了新记录，但数据库却没有。
这里使用不带参数的 AddNew 方法接着设置各个字段的值，最后使用 Update() 方法把新字段更新到数据库，这种方法较为简单。此外，还可以使用带两个参数的 AddNew 方法直接把新记录添加到数据库或使用 Insert Into 查询。比较而言，Insert Into 比较复杂，尤其是在需要处理多个字段、值保存在变量中或各字段的类型不同时。

修改记录
假设当我们添加计算机 atl-ws-99 到数据库后接着 Ken Myer 立即就从 Human Resources 部门转到 Finance 部门，也就是说我们现在需要更新这个计算机的部门字段。放松点，修改记录和添加新记录一样地简单，并且同样可以使用多种方法。不过这里只介绍一种最简便的方法：找到要更新的记录，更新适当的字段，然后调用 Update 方法。

Code: Select all

adOpenStatic := 3
adLockOptimistic := 3

objConnection := ComObjCreate("ADODB.Connection")
objRecordSet := ComObjCreate("ADODB.Recordset")

objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = c:\scripts\inventory.mdb")

objRecordSet.Open("SELECT * FROM GeneralProperties", objConnection, adOpenStatic, adLockOptimistic)

strCriteria := "ComputerName = 'atl-ws-99'"

objRecordSet.Find(strCriteria)
objRecordset.Fields.Item["Department"].Value := "Finance"
objRecordset.Update

objRecordSet.Close
objConnection.Close

您会发现，这段脚本和添加新记录的脚本在前面部分完全相同：连接到数据库，查询数据库并返回记录集。接下来就不一样了：

Code: Select all

strCriteria := "ComputerName = 'atl-ws-99'"

因为我们要更新计算机名为 atl-ws-99 的那条记录，所以这里把搜索条件赋值给 strCriteria 变量。注意这个搜索条件的格式：字段名 = 值。
接着使用 Find 方法找到这个记录：

Code: Select all

objRecordSet.Find(strCriteria)

然后使用两行代码更新这个记录的 Department 字段并保存到数据库：

Code: Select all

objRecordset.Fields.Item["Department"].Value := "Finance"
objRecordset.Update

注意这里引用 Department 字段的方法：objRecordset.Fields.Item["Department"].Value。另外不要忘记了 Update 方法，否则更新不会保存到数据库中。

小结
现在执行整段代码将得到一系列计算机名列表。前面我们对这段代码的每个部分都进行了详细的说明，包括游标类型、锁定类型、如何在记录集中移动、引用记录集中的字段、添加新记录和修改记录，这些都是基础。

[tmplinshi]

一些我碰到的难题及解决方法:

查找数据为空的字段
我测试的情况是，用 字段名 = '' 或者 字段名 IS NULL 不总是有用。
解决方法: IIF(字段名 = '', NULL, 字段名) IS NULL

限制返回的结果数量
在 SQLite 中可以用 LIMITE 数量，但在 Access 数据库没有这个命令。
解决方法: 用 TOP。例如 SELECT TOP 1 * FROM TestTable 则返回 1 个结果。

删除第一个结果
试过 DELETE TOP 1 * FROM TestTable，但无效。
解决方法: DELETE * FROM (SELECT TOP 1 * FROM TestTable)

修改第一个结果
UPDATE (SELECT TOP 1 * FROM TestTable WHERE 年龄='20') SET 真实姓名='张三',年龄='30'