In MS Access VBA (2007), I wrote the following functions to convert a DAO record set to a disabled ADO record set in memory. The problem is that I am having problems converting data types to dbDecimal DAO. The problem occurs when I try to insert data from a DAO record set into a newly created ADO record set. When I get to the DAO dbDecimal (ADO adNumeric) type column, I get the following error:
Error -2147217887 (80040e21):
Multiple-step operation generated errors. Check each status value.
I looked and an error occurs every time it falls into this column. The data contained in this column are primes such as 25, 44, 60, etc.
As you can see below, I hardcoded my NumericScale and Precision, but that doesn't 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 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
DAO ODBC, MS SQL Server 2008. SQL Server Decimal (19,4).
, ?