Multiple-Step OLE DB operation generated errors

[lameid]

I am getting the error "Multiple-Step OLE DB operation generated errors" while trying to update a field in an ADO recordset.

My procedure takes a SQL statement to open a DAO recordset, also takes parameters for columns to match for criteria and columns to update based on. Assumption is field names are the same in source SQL and target table.

The While loop, loops over the DAO recordset and then updates the corresponding ADO (SQL Server) recordset. This code works through the first pass but on the second fails to update the record and I am at a loss. I a search on this error finds that this error is typically associated with connection string but I make it through one update so that does not seem to be the case. I have to admit this error is new to me. Thanks for any help or insight as right now I am a deer in headlights on this.

Error location noted in code below by [red]red comment text[/red].

Global Const gConSQLServer = "[green]ServerAndInstanceIncludingPortHere[/green]" 'Prod Server

Global Const gConSQLDB = "[green]MyDatabaseHere[/green]"


Global Const gConSQLConnect = "Provider=sqloledb;Data Source=" & gConSQLServer & ";" & _
                              "Initial Catalog=" & gConSQLDB & ";" & _
                              "Integrated Security=SSPI;"

Public Sub ADOUpdateFromDAO(strDAOSQL As String, _
    strTargetTable, _
    strMatchFields, _
    strUpdateFields, _
    Optional strConn As String = gConSQLConnect _
  )
  
  'Native Client Connection String - Using Built-in SQL driver as default instead
  '"Provider=SQLNCLI11;" & _
    "Server=VA10N50712\CON01,20001; Database=PharmacyClinicalOperations;" & _
    "Trusted_Connection=yes"
    
  Dim conn As ADODB.Connection
  
  Dim rst As DAO.Recordset
  Dim rstSQL As ADODB.Recordset
  Dim lngRecordsChanged As Long
  Dim strTable As String
  Dim fld As DAO.Field
  Dim aMatchFields() As String
  Dim aUpdateFields() As String
  Dim aDelimeter() As String
  Dim i As Long
  Dim strCriteria As String
  
  aMatchFields = Split(Replace(strMatchFields, ", ", ","), ",")
  aUpdateFields = Split(Replace(strUpdateFields, ", ", ","), ",")
  
  ReDim aDelimeter(UBound(aMatchFields))
  
    
  Set conn = New ADODB.Connection
  conn.Open strConn
  
  Set rst = fnThisDB(blRefresh:=True).OpenRecordset(strDAOSQL) '"tqry_DAOToADO")
  
  
  Set rstSQL = New ADODB.Recordset
  
  
  For i = 0 To UBound(aMatchFields)
    Select Case rst.Fields(aMatchFields(i)).Type
    
      Case dbBigInt, dbBinary, dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbGUID, dbInteger, dbLong, dbLongBinary, dbNumeric, dbSingle 'Numeric
        aDelimeter(i) = ""
      Case dbDate, dbTime
        aDelimeter(i) = "'"  '"#" 'Hash is delemiter for DAO, single quote for ADO
      Case Else
        aDelimeter(i) = "'"
    End Select
  Next i
  
  While Not rst.EOF
    
    strCriteria = ""
    
    For i = 0 To UBound(aMatchFields)
      
      
      strCriteria = addcriteria(strCriteria, aMatchFields(i) & " = " & aDelimeter(i) & rst.Fields(aMatchFields(i)).Value & aDelimeter(i))
      
    Next i
    
    
    rstSQL.Open "SELECT * FROM " & strTargetTable & " WHERE " & strCriteria, conn, adOpenDynamic, adLockOptimistic
    'rstSQL.Find strCriteria
    
    For i = 0 To UBound(aUpdateFields)
      rstSQL.Fields(aUpdateFields(i)).Value = rst.Fields(aUpdateFields(i)).Value [red]'Fails here on second pass through while loop[/red]
    Next i
    
    rstSQL.Update
    
    rstSQL.Close
    rst.MoveNext

  Wend

'Cleanup
  rstSQL.Close
  Set rstSQL = Nothing
  
  conn.Close
  Set conn = Nothing
  rst.Close
  Set rst = Nothing
  
End Sub

Public Function addcriteria(ByVal strExistingCriteria As String, ByVal strAdditionalCriteria As String) As String
    'ANDS new criteria to existing criteria, returns string
    'Fundamental intent is to work with Where clause without the Where, so this is useful for domain aggregate functions such as Dlookup in addition to building SQL Where clause
    If Len(strExistingCriteria & "") = 0 Then
        addcriteria = strAdditionalCriteria
    Else
        addcriteria = "(" & strExistingCriteria & ")" & " and " & strAdditionalCriteria
    End If
End Function

 

[dhookom]

I would add some debug.print statements to confirm the field names and values.

I don't have the time at the moment to try to reverse engineer your code . My day job wins out.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[lameid]

I found a post somewhere that pointed me in the right direction... data is too big for the field. In this case 23 characters for a varchar(20) field. I guess my "column did not resize" issue just became my number one problem up from number two. Thanks for looking Duane.


"Multiple-Step OLE DB operation generated errors" Solved
Seems to be popularly caused by bad connection string.
For me it was a value overrun - or data too big to fit in field.