Hi,

This is because you have two links to the db open at the same time,

what I would suggest is that you either do not use rst2 at all - and just requery each time you want to restrict the value - ie
set rst2 = dbs.OpenRecordset("select * from pool_Data where route_number = '" & strRoute & "';", dbopendynaset)

however what you are doing looks as though you would be able to do it with a simple update query, not worrying about stuffing around with recordsets.

Thanks for the reply Raynard7 ....

>>>>>This is because you have two links to the db open at the same time,<<<<<

Are you referring to rst2 and rstFiltered which are both pointed to the Pool_Data table???

>>>what I would suggest is that you either do not use rst2 at all - and just requery each time you want to restrict the value - ie <<<<

Are you saying don use the rstFiltered and just requery rst2 based on the dynamic route number from rst???

Thanks,

ET

yes, that is what I am saying would work,

however I would recommend that you do this through SQL instead of VB - it would be faster and more reliable.

Even with rstFiltered out of the loop and requering rst2 with the route number from rst I still get the same run-time error 3197.  Does this mean that in that function you cannot have more than one recordset open to the MySQL back end even if they are using different tables???

I know I can use SQL but this is a migrated application from Access to MySQL and there's probably many cases where multiple recordsets have been opened in the code.  I've just started to test the front end and I don't want to have to rewrite all those functions.


Set rst = dbs.OpenRecordset("TempPool", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
    strRoute = rst!ROUTE_NUMBER
    Set rst2 = dbs.OpenRecordset("select * from pool_Data where route_number = '" & strRoute & "';", dbOpenDynaset)
    rst2.MoveLast
    stopcnt = rst2.RecordCount
    rst2.MoveFirst
        Do Until rst2.EOF
            rst2.Edit
            rst2!TOTAL_STOPS = stopcnt
            rst2.Update     <-------------------------------Still generates the error
            eqsum = eqsum + rst2!STOP_EQ
            rst2.MoveNext
        Loop

    rst.MoveNext
Loop


Thanks,

ET

Just a thought but could it be because you are not closing your recordsets properly?

U keep opening rst2 but I dont see where u close it after the loop. I wonder if because u keep opening these recordsets but not closing them, that might have a impact of some sort.

Also u shouldnt do a MoveLast straight away, instead check for EOF
eg


    Set rst2 = dbs.OpenRecordset("select * from pool_Data where route_number = '" & strRoute & "';", dbOpenDynaset)
   
    'NEW LINE if statement
    If rst2.EOF = False Then
        rst2.MoveLast
        stopcnt = rst2.recordCount
        rst2.MoveFirst
        Do Until rst2.EOF
            rst2.Edit
            rst2!TOTAL_STOPS = stopcnt
            rst2.Update
            eqsum = eqsum + rst2!STOP_EQ
            rst2.MoveNext
        Loop
   
    'NEW 2 LINES endif/close
    End If
    rst2.Close
       
    rst.MoveNext



it may not solve your problem but its something I believe u should do

Thanks for the reply rockiroads ... and all taken into consideration but even when I try this using only one recordset and fill in the variables I still get the error that someone else is attempting to change the same data.  It just seems like MySQL doesn't like that table being edited by a recordset.  I can do it with SQL  but I'm trying to figure out what's going on here.  Surely the function below should be able to execute successfully without generating that error.  

Maybe there is something or a field setting in the table causing this or some other parameter that needs to be included on the OpenRecordset to eliminate this.

Function TestRST()
    Dim rst2 As Recordset, dbs As Database
    Set dbs = CurrentDb
    strRoute = "01000"
    Set rst2 = dbs.OpenRecordset("select * from pool_Data where route_number = '" & strRoute & "';", dbOpenDynaset)
    stopcnt = 1
    rst2.Edit
    rst2!TOTAL_STOPS = stopcnt
    rst2.Update     '<-------------------------------Still generates the error
End Function


Thanks,

ET

Ok, here is what I did manage to figure out but not sure how to correct it in MySQL.  I opened the Pool_Data table (the table being updated) in the front-end just browsing for possible corrupt records.  I can manually go to the record the recordset is trying to update and change it then save it with no problems.  I did notice where there is another record in this table that I cannot modify or change without getting the Write Conflict error "The record has been changed by another user since you started editing it ........".

This tells me that that record or records is corrupted in that table and is why the recordset update is failing.

Question:  Is it possible to repair that table in MySQL and if so how???

Thanks,

ET

Ok, I did a small test. Just got MySQL working

I defined my variables by specifying the right model (DAO, ADO)

I reproduced your error by defining it like this

Dim rst2 As Recordset, dbs As Database


I originally I had it like this

Dim rst2 As DAO.Recordset, dbs As DAO.Database

and I never got the error

Thanks rocki ....

Tried that as well (see below) and still got the error message ...
I think it probably has something to do with the table in the MySQL backend but nothing stands out.  I even deleted the record that would not let me edit it directly in the front-end linked table to no avail.  The table has a PK which is the pool_id field and I added an additional ID auto increment  still to no avail.

Function TestRST()
    Dim rst2 As DAO.Recordset, dbs As DAO.Database
    Set dbs = CurrentDb
    strRoute = "01000"
    Set rst2 = dbs.OpenRecordset("select * from pool_Data where route_number = '" & strRoute & "';", dbOpenDynaset)
    stopcnt = 1
    rst2.Edit
    rst2!TOTAL_STOPS = stopcnt
    rst2.Update     '<-------------------------------Still generates the error
End Function

hmmm, is it possible for u to dump the schema of that table? I can try it on my instance. It may or may not be data related or table locks.

Have u ever heard of Navicat? its a excellent tool for managing mysql databases

lets try an alternative way (also, can u confirm that route_number is defned as a string? and TOTAL_STOPS is numeric?)

set rst2 = dbs.OpenRecordset("pool_data")
rst2.FindFirst "route_number = '" & strRoute & "'"
if rst2.NoMatch = True then
    msgbox "Record not found"
else
    rst2.Edit
    rst2!TOTAL_STOPS = 50
    rst2.Update
end if

Hello rocki ...

Yes Route_number is Text/String and TOTAL_STOPS is numeric.

Even trying the alternative method I got the exact same results including the rst2.Close at the end of the function.  It will run the very first time but the next time you execute the function the error will pop up about someone else is changing the data, etc.  It's like once the recordset edits the table and closes the recordset MySQL still thinks the table/record is being edited.  Strange but I'm trying to make this work with the Recordset option because I know there are many more functions that's doing the same thing and I really don't want to go in and re-write all of them.


    Set rst2 = dbs.OpenRecordset("pool_data")
    rst2.FindFirst "route_number = '" & strRoute & "'"
    If rst2.NoMatch = True Then
    MsgBox "Record not found"
    Else
    rst2.Edit
    rst2!TOTAL_STOPS = 50
    rst2.Update
    rst2.Close
    End If

I know how to dump the entire db using mysqldump but not really sure how to create a schema of just that table plus this is a large table with lots of fields (60+).

ET

Ok rocki ......

Hummm,  I see the pattern now.  I ran into this before with a control on a form being updated from a subform where the recordsource was tied to a MySQL Backend.  It's real weird and I never found a solution or explanation as to why this happens.

Here goes ....

When the recordset is updating a field in the MySQL backend, if the value in the field (TOTAL_STOPS) in this case will actually be changed by the update (meaning not the same) then the recordset update will work without generating the error.  If the value in the field will remain the same as what the recordset is trying to update it to then it's like MySQL doesn't do anything and is left in a state where it thinks the record is still being edited.

I'm sure there is a way to get around something that seems so simple but who knows.  

ET

Well if it thinks its the same, try putting a if condition in
only update if the values differ

I remember your last question, yes weird one that.

puttng the if condition - It was just as a test to prove your theory

Yes, that's how I got around it on the form the first time I ran into this issue but I'm thinking there is something I'm missing in the setup of the table that's possibly causing this.  I don't think that's normal behavior and is the reason for trying to find out how to correct this.

I posted another related question to see if anyone else has run into this problem.

Thanks,

ET

Ok, I see what u mean now as Ive reproduced your problem
Now that is strange. I wonder if there is a bug in MySQL

I tried the example using ADO and its the same problem.

Im checking mysql support to see if there is anything
Also read somewhere about service packs, Im assuming you have all the latest windows service packs and latest mysql

Ha!!!  Works like a charm rocki.   Again, that's why you're the "King".  I hadn't made it to the DSN with my analysis but I just kept thinking there's got to be some setting somwhere that I am missing that's causing this.  It just seemed logical.  

"Return Matching Rows" is the answer.  Once checked on in the DSN the Recordset Update works as intended without generating the error.

Much appreciated rocki and I owe you one big time.

Thanks,

ET

Great stuff, this also means I finally answered one of your many tricky questions!