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

问题描述

在MS Access VBA(2007)中，我编写了以下函数，用于将DAO记录集转换为断开连接的内存中ADO记录集.问题是我在DAO dbDecimal字段上遇到数据类型转换问题.当我尝试将数据从DAO记录集插入到新创建的ADO记录集中时，就会出现问题.当我到达DAO dbDecimal(ADO adNumeric)类型的列时，出现以下错误:

In MS Access VBA (2007), I've written the functions below to convert a DAO recordset to a disconnected, in-memory ADO recordset. The problem is that I'm having data type conversion problems on the DAO dbDecimal fields. The problem shows up when I try to insert data from the DAO recordset into the newly created ADO recordset. When I get to the column that is type DAO dbDecimal (ADO adNumeric) I get the following error:

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

我已经查看过，并且每次进入此列时都会发生错误.此列中的数据是简单数字，例如25、44、60等.

I've looked and the error happens every time it gets to this column. The data contained in this column is simple numbers such as 25, 44, 60, etc.

如下所示，我已经对NumericScale和Precision进行了硬编码，但这似乎无济于事.

As you can see below, I've hard-coded my NumericScale and Precision but this doesn't seem to help anything.

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的ODBC链接表中获取我的DAO记录集.该字段实际上是SQL Server Decimal(19,4)数据类型.

I am deriving my DAO recordset from an ODBC Linked table which is linked to MS SQL Server 2008. The field is actually a SQL Server Decimal(19,4) data type.

有什么办法解决这个问题吗?

Any ideas how to get around this problem?

推荐答案

这对我有用，但是我不确定为什么不只是从表中创建ADODB记录集并断开连接.

This works for me, but I am not sure why you are not just creating the ADODB recordset from the table and disconnecting it.

  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

这篇关于将DAO记录集转换为断开连接的ADO记录集dbDecimal问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！