Hi

I'm trying to update one recordset with another but keep getting this error msg:
"Run-time error '-2147217887(80040e21)
Multiple-step OLE DB operation genarated errors. Check each OLE DB status value, if available."

It all comes to a halt at:
rs1(9) = rs2!ID

I had a problem where rs1!ID was not recognised but accepted the field index.
rs1 is an ADO connection and rs2 is DAO.
I can see the values being rs1(9)=Null and rs2!ID="D001".

Anyone know how to correct this error?

Cheers / Fred

Hi Fred

Try to use ADO connection and Recorset or DAO connection and Recordset

-vemaju

Hi Fred,

There is nothing, on the face of it, that would prevent you from updating a value in an ADO recordset with a value from a DAO recordset. I suspect the problem has to do with the properties of your ADO recordset object, particularly CursorType or CusorLocation. Be sure you're using an updateable cursor (Keyset or Dynamic) and that you're using a client-side cursor. The default settings for an ADO recordset give you a forward-only, server-side cursor, which wouldn't work for your purposes, so you'll need to set those properties explicitly, if you haven't already.

The "rs1!ID was not recognised but accepted the field index" is puzzling. If the recordset's data source actually contains a field named "ID", I can't think of any reason why you would'nt be able to reference it using "rs1!ID".

Post some code that occurs earlier in your module, such as connection and recordset object configurations if you like. It would be easier to see whats actually going on.

HTH,

Bob

Hi Bob

Thanks for taking a look at this. Here's the code:

Dim cn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String
Set cn1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset

Dim db As DAO.Database
Dim rs2 As DAO.Recordset
Dim rsPart As DAO.Recordset
Dim intRecords As Integer
Set db = CurrentDb()


'Open stock query

Set rs2 = db.OpenRecordset("qry_stock", dbOpenDynaset)


 'Opens connection to Whapp

cn1.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "SERVER=192.168.0.210;" & _
                 "DATABASE=whapp;" & _
                 "USER=stock;" & _
                 "PASSWORD=;"

' Open tbl_prod_pick to select ID's

strSQL1 = "tbl_prod_pick"
rs1.Open strSQL1, cn1, adOpenDynamic, adLockOptimistic, adCmdTable
rs1.Filter = "Ppo ='" & txtPpo & "'"


'Add ID and ID qty to tbl_prod_pick

rs1.MoveFirst
Do Until rs1.EOF
   counter = counter + 1
   rs2.Filter = "PART='" & rs1!PART & "'"
   rs2.MoveFirst
 If rs2!ID = Null Then
   rs1.MoveNext
   Else
   rs1(9) = rs2!ID
   rs1(11) = rs2!QTY
   rs1.Update
   rs1.MoveNext
   End If
Loop

Like I mentioned before, when it comes to rs1(9) = rs2!ID I always get some sort of problem. I've even tried to strip the code down to exclude the DAO code and just using a simple rs1(9)="test" to see if the DAO stuffed it up, but I still get errors.

Cheers / Fred

Hi Bob

I tried to stripp the code down even further:

Dim cn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim Command As ADODB.Command
Dim strSQL1 As String
Set cn1 = New ADODB.Connection
Set rs1 = New ADODB.Recordset

 'Opens connection to Whapp

cn1.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "SERVER=192.168.0.210;" & _
                 "DATABASE=whapp;" & _
                 "USER=stock;" & _
                 "PASSWORD=;"

' Open tbl_prod_pick to select ID's
strSQL1 = "tbl_prod_pick"
rs1.Open strSQL1, cn1, adOpenDynamic, adLockOptimistic, adCmdTable
rs1.Filter = "Ppo ='" & txtPpo & "'"

'Add ID and ID qty to tbl_prod_pick
rs1.MoveFirst
Do Until rs1.EOF
   rs1(9) = "Test"
   rs1.Update
   rs1.MoveNext
   Loop

Now I get a different error when it halts at "rs1.Update" saying:

Run-time error '-2147467259(80004005)

[MySQL][ODBC 3.51 Driver][mysqld-5.0.0-alpha-nt] You have an error in your SQL syntax. Check the manual that corresponds to your MYSQL server version for the right syntax to use near'Group IS NULL AND Ppo=' 1253,000' AND Sku='PS434AA, T860A, AN

Huh?

Cheers / Fred

Hmmmm.......I can move the cursor and print the values in the immediate window for the table in rs1, but the recordcount returns -1.

Hi Fred,

Your record count is -1 because you are using a dynamic cursor. While it may seem a bit odd, not all ADO cursor types are capable of maintaining an accurate record count. Only static and keyset cursors can tell you how many records they contain. Forward-only cursors will also return -1.

A dynamic cursor is the only cursor type that can retrieve records added by other users of your db since the last time you executed a query, requesting records to fill the cursor. So there's no guarantee that the number of records in the cursor will remain constant each time ADO fills its cache. Rather than report an erroneous record count (their may be recently added records in the underlying data source whose keys are'nt included in the dynamic cursor), ADO just returns -1.

If you want both the dynamic cursor's ability to see records added by other users and a record count, execute a SQL statement like the following after opening the recordset:

strSQL = "Select Count(ID) FROM YourTable"
Set rstRecordCount = cnn.Execute(strSQL, , asCmdText)
lngRecordCount = rstRecordCount(0).Value
rstRecorCount.Close

If you don't care if you see records added by other users, use a Keyset cursor, which will at least allow you to see changes made by other users to records in the key set, or a static cursor (pretty much a "forward-only" cursor, 'cept that it also lets you scroll backwards). Both can tell you how many records they contain.

HTH,

Bob

Oh, and with regards to my previous comment about updateable cursors, keyset and dynamic cursors are the only server-side cursors that support updating. However, if you got things working using a client-side cursor, ADO will tell you you are using a static cursor, but the records it contains are still updateable. The static data is maintained by the ADO Client Cursor Engine on your client machine, which is smart enough to find changes in the data, translate those changes into action queries, and update your data store when you call the recordsets Update or UpdateBatch methods. Plus you can get a record count because you're cursor is static. You can even disconnect it if you want because its client-side.

Bob

Hi Bob

Thanks for your replys.
After searching around on the internet all day I've changed my open string to:
rs1.CursorLocation = adUseClient
rs1.Open strSQL1, cn1, adOpenDynamic, adLockOptimistic, options = " & (1+2+8+32+2048+16384)"

What that does is enabling me to make updates to records as long as the new value is shorter than the old value.
If both values are the same size I get the following error msg:

Run_time error '-2147217864 (80040e38)':
Row cannot be located for updating. Some values may have been changed since it was last read.

And if the new value is longer than the old value I get:

Run-time error '-2147217887 (80040e21)':
Multiple-step operation generated errors. Check each status value

Really silly problem.
Haven't really found a solution to the field size problem as yet.

Cheers / Fred