vba操作数据库-RecordSet的使用

前言

ADODB用来操作数据库是非常方便的。用Recordset对象，可以直接增删改数据库而不用使用SQL ADD/UPDATE/DELETE语句. 这个系列结合自己的实践，全面记录一些使用细节。

ADODB的关键组件

Recordset

1. 定义

2. 取得
   可以用connection.execute()方法取得一个recordset, 但是注意这样取得的是只读的，不可修改。
   推荐用rs.open()来获取。

3. CursorType

4. CursorLocation
   

5. LockType
   

6. 错误
   
   当对recordset做更新到第9427笔时，出现以下错误提示
   

对于Access, 当CursonLocation 设为adUseClient 时，即使LockType设成adLockBatchOptimistic, 改动recordset值，还是会立即反映到数据库而不是批量处理
当CursonLocation 设为adUseServer 时，LockType设成adLockBatchOptimistic, 可以批量处理，但是会遇到exceed limit错误，即使手动update也不能解决

8. AddNew 方法插入记录
recordset.AddNew FieldList, Values
两个参数可以是单值，也可以是数组。显然用数组比较方便

    Dim arrTitle(1 To 2), arrValue(1 To 2) 'arrValue(1 To 3, 1 To 2)
    arrTitle(1) = "InvNo"
    arrTitle(2) = "Account"
    arrValue(1) = "Inv#001"
    arrValue(2) = "关税"
         
    rsAcc.CursorType = adOpenDynamic
    rsAcc.LockType = adLockOptimistic 'adLockBatchOptimistic
    rsAcc.CursorLocation = adUseServer

    rsAcc.Open "select InvNo,Account from Invoices", cnn, adOpenDynamic, adLockOptimistic
    rsAcc.MoveLast
    rsAcc.AddNew arrTitle, arrValue

另一种方法是用With语句，更清晰

Dim arrValue(1 To 3, 1 To 2), intLine%
'准备数组
For intLine = 1 To 3
   arrValue(intLine, 1) = "Inv#00" & intLine
   arrValue(intLine, 2) = "关税"   
Next intLine
'准备一个空的RecordSet
rsAcc.Open "select * from Invoices where invno = ''", cnn, adOpenDynamic, adLockOptimistic

For intLine = 1 To 3
       With rsAcc
           .AddNew
           .Fields("InvNo") = arrValue(intLine, 1)
           .Fields("Account") = arrValue(intLine, 2)
           .Update
       End With
Next intLine

AddNew 一次只能插入一条记录
更多参考资料
Adding Records Using AddNew Method
Recordset.AddNew method (DAO)
Using AddNew in Immediate and Batch Modes
9. EditMode

10. 批量更新 与立即更新

• LockType = adLockBatchOptimistic + CursorLocation = adUseClient时，即使addnew后立即使用update, 也不会真正update, 要执行updatebatch后才会写到数据库
  下面这个例子中, 虽然CursorType指定的是adOpenDynamic (value = 2), 但实际上系统给的是3 (adOpenStatic)

   With rsAcc
       .CursorLocation = adUseClient
       .CursorType = adOpenDynamic
       .LockType = adLockOptimistic
   End With
   For intLine = 1 To 3
       With rsAcc
           .AddNew
           .Fields("InvNo") = arrValue(intLine, 1)
           .Fields("Account") = arrValue(intLine, 2)
           .Update    '实际没有写入数据库, 改成 MoveNext也是一样
       End With
   Next intLine
   
   rsAcc.UpdateBatch  '到这里才真正写后台数据库

• 如果将LockType改成adLockOptimistic, CursorLocation 同样是 adUseClient，则addnew后使用update或movenext, 会立即更新

• 对于Access, CursorLocation = adUseServer + LockType = adLockBatchOptimistic无法工作。addnew一笔后, 只有执行UpdateBatch后，才能继续addnew, 否则会报错Number of rows with pending changes exceeded the limit.:
  

说明LockType 设为adLockBatchOptimistic后，就只能用UpdateBatch才生效

• 对于Access, CursorLocation = adUseServer + LockType = adLockOptimistic, addnew后用update, 则立即更新到数据库。用UpdateBatch效果与Update一样, 用MoveNext也会立即触发更新
• 参考资料
  Using AddNew in Immediate and Batch Modes

12. Delete方法
    recordset.Delete
    删除游标所在的那笔记录。
    如果批量删除，那还是用SQL来得方便

13. RecordCount属性
    对于Access数据库, CursorLocation = adUseClient时，可以使用recordset.RecordCount属性。CursorLocation = adUseServert时，RecordCount属性返回 -1.

14. Update记录与AddNew类似, CursorLocation = adUseClient + LockType = adLockBatchOptimistic组合，更新字段后MoveNext或Update, 不会触发实际更新。只有UpdateBatch后，才真正更新

15. 将RecordSet数据提取到数组

• 可以用RecordSet.GetRow(行数) 函数
  GetRow()函数返回一个二维数组，注意它的第一维是字段，第二维是记录行。相当于是一个转置的表。代码参考
• 曲折办法, 先将RecordSet拷贝到Excle中的Range, 再从Range转为数组
  Range.CopyFromRecordset 方法 (Excel) 注意是从RecordSet的当前行(游标所在行)开始，拷到最后一行。
  从range转为数组非常简单，使用Range.Value属性赋值给数组即可, 注意得到的数组下标是从1开始的，不是从0开始的
  如下例, Range数据如下
  

Sub test()
	Dim rg As Range, arr()
	
	Set rg = Sheets("sheet1").Range("C3:F8")
	arr = rg.Value
	
	Debug.Print UBound(arr, 1) & "x" & UBound(arr, 2)
	Debug.Print arr(2, 2)
End Sub

输出结果如下:

6x4
Row2 / Col:2

反过来，把数组值给Range时，需要正确地指定Range的大小，数组与range大小不一致时，规律如下:
(1) 对于重合的部分，取数组中的值
(2) 对于 数组比range多出的部分，忽略
(3) 对于数组比range少的部分，补#N/A
如下图所示

16. D
17. D
18. D
19. D