将DAO记录集转换为断开连接的ADO记录集dbDecimal问题

Question

在MS Access VBA（2007）中，我编写了下面的函数来将DAO记录集转换为断开连接的内存ADO记录集。问题是我在DAO dbDecimal字段上有数据类型转换问题。当我试图将数据从DAO记录集插入新创建的ADO记录集时，问题就出现了。当我到是类型DAO dbDecimal（ADO adNumeric）我得到以下错误列：

Error -2147217887 (80040e21): 
Multiple-step operation generated errors. Check each status value. 

我看了一下，错误发生在每次到达此列的时间。此列中包含的数据是简单的数字，例如25,44,60等。

正如您在下面看到的，我对我的NumericScale和Precision进行了硬编码，但这似乎没有任何帮助。

Public Function ConvertDAORStoADORS(ByRef r1 As DAO.Recordset) As ADODb.Recordset 

    If Not r1 Is Nothing Then 

     Dim ra As ADODb.Recordset 
     Set ra = New ADODb.Recordset 

     Dim f1 As DAO.Field, fa As ADODb.Field 

     For Each f1 In r1.Fields 
      Select Case f1.Type 
       Case dbText 
        ra.Fields.Append f1.Name, adVarWChar, f1.Size, adFldIsNullable 
       Case dbMemo 
        ra.Fields.Append f1.Name, adLongVarWChar, 10000, adFldIsNullable 

       'Here's the problematic one 
       Case dbDecimal 
        ra.Fields.Append f1.Name, adNumeric, , adFldIsNullable 
        Set fa = ra.Fields(f1.Name) 
        fa.NumericScale = 19 
        fa.Precision = 4 

       Case 9, dbLongBinary, dbAttachment, dbComplexByte, dbComplexInteger, dbComplexLong, dbComplexText, dbComplexSingle, dbComplexDouble, dbComplexGUID, dbComplexDecimal 
        'Unsupported types 
       Case Else 
        Debug.Print f1.Name & " " & f1.Type 
        ra.Fields.Append f1.Name, GetADOFieldType(f1.Type), , adFldIsNullable 

      End Select 
     Next f1 
     ra.LockType = adLockPessimistic 
     ra.Open 
     'On Error Resume Next 
     If Not (r1.EOF And r1.BOF) Then 
      r1.MoveFirst 
      Do Until r1.EOF = True 
       ra.AddNew 
       For Each f1 In r1.Fields 
        'Error -2147217887 (80040e21) Multiple-step operation generated errors. Check each status value. 
        'Error only occurs on dbDecimal/adNumeric fields 
        ra(f1.Name).value = r1(f1.Name).value 
       Next f1 
       ra.Update 
       r1.MoveNext 
      Loop 
     End If 

     Set ConvertDAORStoADORS = ra 
    End If 

End Function 

Private Function GetADOFieldType(daofieldtype As Integer) As Long 
    Select Case daofieldtype 
     'Fixed width adWChar does not exist 
     Case dbText: GetADOFieldType = adVarWChar 
     Case dbMemo: GetADOFieldType = adLongVarWChar 
     Case dbByte: GetADOFieldType = adUnsignedTinyInt 
     Case dbInteger: GetADOFieldType = adSmallInt 
     Case dbLong: GetADOFieldType = adInteger 
     Case dbSingle: GetADOFieldType = adSingle 
     Case dbDouble: GetADOFieldType = adDouble 
     Case dbGUID: GetADOFieldType = adGUID 
     Case dbDecimal: GetADOFieldType = adNumeric 
     Case dbDate: GetADOFieldType = adDate 
     Case dbCurrency: GetADOFieldType = adCurrency 
     Case dbBoolean: GetADOFieldType = adBoolean 
     Case dbLongBinary: GetADOFieldType = adLongVarBinary 
     Case dbBinary: GetADOFieldType = adVarBinary 
     Case Else: GetADOFieldType = adVarWChar 
    End Select 
End Function 

我从链接到MS SQL Server 2008中的字段实际上是一个SQL Server十进制（19,4）数据类型的ODBC链接表导出我的DAO记录。

任何想法如何解决这个问题？

Answer 1

这适用于我，但我不确定为什么你不只是从表中创建ADODB记录集并断开连接。

Case dbDecimal 
     ra.Fields.Append f1.Name, adDecimal, , adFldIsNullable 
     Set fa = ra.Fields(f1.Name) 
     fa.NumericScale = 19 
     fa.Precision = 4 

而且，为什么不

Dim ra As New ADODb.Recordset 
    ''Set ra = New ADODb.Recordset