向MS Access ADO记录集追加记录时出现问题-记录消失

Question

使用MS Access 2013:

我正在创建一个独立的ADO记录集，作为我正在编写的应用程序的内存写入缓冲区。通常我会使用表/查询，但多人同时使用数据库。如果我把这些记录写到一个表中，用户有可能会覆盖彼此的数据。

ADODB。Recordset被定义为一个全局变量，并使用函数进行初始化。现在，这里是棘手的部分。我可以定义记录集很好，但是当我添加第一个记录时，记录在添加后"消失"。

我使用while - end循环来添加记录。当我进入该循环的中间时，我可以移动到不同的记录并进行调试。打印任何我喜欢的值。但是一旦我退出循环，我就失去了所有的记录。任何调试。无论我移动到哪里，print命令都会产生"无当前记录"。当我在其他函数中调用全局变量时，我得到一个空记录集。

代码如下:

Option Compare Database
'Establish a global recordset variable to be a write buffer
Public gbl_rstADO_WriteBuffer As ADODB.Recordset
Option Explicit
Private Function InitWriteBuffer()
Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim j As Integer
'These records will form the structure and initial entries of the write buffer
strSQL = "SELECT [#DEFAULT MASTER QUERY].* FROM [#DEFAULT MASTER QUERY] INNER JOIN " & _
"tblBatchCircuitUploadTable ON [#DEFAULT MASTER QUERY].[Install ID] = " & _
"tblBatchCircuitUploadTable.[Install ID] WHERE (((tblBatchCircuitUploadTable.[Install ID]) Is Not Null));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Set gbl_rstADO_WriteBuffer = New ADODB.Recordset
'Create detatched recordset using fields from SQL query
For i = 0 To rst.Fields.Count - 1
    gbl_rstADO_WriteBuffer.Fields.Append rst.Fields(i).Name, adVariant, , adFldMayBeNull
Next
With gbl_rstADO_WriteBuffer
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With

rst.MoveFirst
'Copy values from DAO recordset, one-by-one, into the detatched ADO recordset
While Not rst.EOF
    gbl_rstADO_WriteBuffer.AddNew
    'Move through values of current source record, and copy one by one to ADO destination
    For j = 0 To gbl_rstADO_WriteBuffer.Fields.Count - 1
        gbl_rstADO_WriteBuffer.Fields(j).Value = rst.Fields(j)
    Next
    gbl_rstADO_WriteBuffer.Update
    rst.MoveNext
Wend
    'With the following command I receive a "No Current Record" error
    'Moving to first, last, etc in the immediate window all still produce "no current record"
    Debug.Print gbl_rstADO_WriteBuffer.Fields(0).Value

'Cleanup
Set rst = Nothing
Set dbs = Nothing
End Function

有人看到我错过了什么吗?

Answer 1

非常感谢您的反馈!所有的点都很好。

就技术答案而言，HansUp拥有它:结果是您不能将adVariant数据类型附加到ADO记录集中。它不被支持。瘸腿的。

因此，如果您倾向于将记录从DAO记录集复制到ADO记录集以便将其分离，那么在创建ADO记录集时，将数据类型从一个映射到另一个会有所帮助。

这是我想到的:

...
For i = 0 To rst.Fields.Count - 1
    intDataType = IDS_MapToTypeADO(rst.Fields(i).Type)
    If intDataType = adVarWChar Then
        gbl_rstADO_WriteBuffer.Fields.Append rst.Fields(i).Name, intDataType, 50, adFldMayBeNull
    Else
        gbl_rstADO_WriteBuffer.Fields.Append rst.Fields(i).Name, intDataType, , adFldMayBeNull
    End If
Next
With gbl_rstADO_WriteBuffer
    .CursorType = adOpenKeyset
    .CursorLocation = adUseClient
    .LockType = adLockPessimistic
    .Open
End With
...
Private Function IDS_MapToTypeADO(iTypeDB As Integer) As Long
Select Case iTypeDB
    'Fixed width adWChar does not exist
    Case dbText: IDS_MapToTypeADO = adVarWChar
    Case dbMemo: IDS_MapToTypeADO = adLongVarWChar
    Case dbByte: IDS_MapToTypeADO = adUnsignedTinyInt
    Case dbInteger: IDS_MapToTypeADO = adSmallInt
    Case dbLong: IDS_MapToTypeADO = adInteger
    Case dbSingle: IDS_MapToTypeADO = adSingle
    Case dbDouble: IDS_MapToTypeADO = adDouble
    Case dbGUID: IDS_MapToTypeADO = adGUID
    Case dbDecimal: IDS_MapToTypeADO = adNumeric
    Case dbDate: IDS_MapToTypeADO = adDate
    Case dbCurrency: IDS_MapToTypeADO = adCurrency
    Case dbBoolean: IDS_MapToTypeADO = adBoolean
    Case dbLongBinary: IDS_MapToTypeADO = adLongVarBinary
    Case dbBinary: IDS_MapToTypeADO = adVarBinary
    Case Else: IDS_MapToTypeADO = adVarWChar
End Select
End Function

这借用了另一个StackOverflow解决方案:将DAO记录集转换为断开连接的ADO记录集dbDecimal问题

再次感谢你。我很感激!