vba操作数据库-RecordSet的使用

大家好，欢迎来到IT知识分享网。

前言

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

ADODB的关键组件

Recordset

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

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

 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 

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 

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