×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Multiple-Step OLE DB operation generated errors

Multiple-Step OLE DB operation generated errors

Multiple-Step OLE DB operation generated errors

(OP)
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 comment text.

CODE

Global Const gConSQLServer = "ServerAndInstanceIncludingPortHere" 'Prod Server

Global Const gConSQLDB = "MyDatabaseHere"


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 'Fails here on second pass through while loop
    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 

RE: Multiple-Step OLE DB operation generated errors

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 hammer. My day job wins out.

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

RE: Multiple-Step OLE DB operation generated errors

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close