exactly I have a linked table named "off" , and I
are opening this linked table by a command "docmd.opentable off" , and trying to
delete the record from there.

the table have one primary unique key with name off_id and 2 other index fields :
"
INDEX NAME FIELD NAME ORDER
off_codice off_codice ascending
off_id off_id ascending
off_tmp_id off_tmp_id ascending
PRIMARY off_id ascending
"

the deletion operation is a simple native deletion,
but the rows of table was written by a vba code that take row from a direct access table, named "off_tmp" and put a copy of this row in the linked table, named "off"
the code is :
"
Set rs = db.OpenRecordset( _
"SELECT off_tmp.* " & _
"FROM off_tmp LEFT JOIN off ON (off_tmp.off_revisione = [off].off_revisione) AND (off_tmp.off_codice = [off].off_codice) " & _
"WHERE ((([off].off_codice) Is Null) AND (([off].off_revisione) Is Null))" _
, dbOpenDynaset)
If Not rs.BOF Then
rs.MoveLast
NR = rs.RecordCount
End If
If rs.BOF Or NR = 0 Then exit sub
rs.MoveFirst
For N = 1 To NR
rs2.AddNew
For N2 = 0 To rs.Fields.Count - 1
campo = CStr(Nz(rs(N2)))
Select Case rs(N2).Type
Case 1 ' boolean
If campo = "Vero" Or campo = "-1" Or campo = "True" Then
rs2(N2) = -1
Else
rs2(N2) = 0
End If
Case 4 ' integer
If campo <> "" And IsNumeric(campo$) Then rs2(N2) = CLng(campo)
Case 7 ' double
If campo <> "" And IsNumeric(campo$) Then rs2(N2) = CDbl(campo)
Case 8 ' data
If campo <> "" And IsDate(campo$) Then rs2(N2) = CDate(campo)
Case Else
If campo = "" Then
rs2(N2) = Null
Else
rs2(N2) = campo
End If
End Select
rs2!off_modDataExiGest = Now()
Next N2
rs2.Update
rs.MoveNext
Next N
rs.Close: Set rs = Nothing
Set db = Nothing

If I understand your code correctly, then you are trying to delete a
record from the same table that rs2 is accessing, possibly even the
exact same record. If you are not using rs2 as the basis for any
controls on a form or report then I would suggest to do one of the
following:
- after the rs2.update you can try to change record, something like
rs2.MoveFirst and in this way the 'focus' is not on the record you
just made

OR

- This is my preferred option: create an SQL statement to INSERT the
record into the rs2 table and then do this via db.Execute. In this way
you wont actually need rs2 at all and there cant be a locking conflict
from this process. Likewise, when you want to do the delete build an
SQL DELETE statement and use db.Execute to run it.

Just a couple of pointers that migght make the app a little more
robust too:
1/ get rid of the off_if index as it is the same as the PRIMARY and is
just wasting processor cycles
2/ change the recordset type of rs from dynaset to snapshot
(dbOpenSnapshot), or even better is to create a view in the db to give
you the data you want, and then in your application create a snapshot
recordset from that to work with (ie/ SELECT * FROM <view name here>
as the basis of your recordset). You can optimise performance around a
view quite well in MySQL. Using a snapshot recordset should also speed
things up a bit.

So you end up with something like this in the end:

dim SQL as String
dim TMP as String

SQL = "SELECT off_tmp.* " _
& "FROM off_tmp " _
& "LEFT JOIN off " _
& "ON [off_tmp].off_revisione = [off].off_revisione " _
& "AND [off_tmp].off_codice = [off].off_codice " _
& "WHERE [off].off_codice Is Null " _
& "AND [off].off_revisione Is Null"

set rs = db.OpenRecordset (SQL, dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
if rs.RecordCount > 0 then
SQL = "INSERT INTO <tablename goes here> ("
For N = 0 to rs.Fields.Count - 1
SQL = SQL & rs.Fields(N).Name & ","
Next
SQL = Left(SQL, Len(SQL) - 1) 'remove the last comma
SQL = SQL & ") VALUES "

'We have the start of the INSERT statement done, now we need the
values

For N = 1 to rs.RecordCount
SQL = SQL & "(" 'start the value set syntax
For N2 = 0 to rs.Fields.Count - 1
TMP = CStr(Nz(rs(N2)))
If rs.Fields(N2).Type = 1 Then 'Boolean
If TMP = "Vero" Or TMP = "-1" Or TMP = "True" Then
SQL = SQL & "-1,"
Else
SQL = SQL & "0,"
End If
Else
If TMP <> vbNullString Then
SQL = SQL & TMP & ","
Else
SQL = SQL & "NULL,"
End If
End If
Next
SQL = SQL & "),"
Next
SQL = Left(SQL, Len(SQL) - 1) 'remove the last comma
db.Execute SQL
End if
rs.Close
set rs = Nothing

I hope that this helps you a bit. I have not checked the code against
a linked table set, I just typed it from my head so please check it
and the SQL it creates. I think it is ok. In effect what this is doing
is eliminating the need for you to have an open recordset that is
keeping any locks on the table(s) in question. You are 'shooting off'
the SQL commands like bullets and 'keeping the wire cold' so to speak
at the same time. You may also want to investigate the use of the
INSERT INTO.......SELECT statement which could in theory do all the
work for you on the database server itself. It might be possible to
come up with a suitable SQL statement that handles the boolean issue
that seems to be possible with your situation. INSERT INTO .....
SELECT would certainly be the fastest way if you can find a way to
implement it. Same as above you would build the SQL statement you
want, and then db.Execute to run it.

Cheers

The Frog

I forgot to mention that with the data types for MySQL, if there is a
String being INSERTed then it needs to be enclosed in single quotes:

SQL = SQL & "'" & TMP & "',"

I cannot remember the numeric type number for String so please add
that into your code. Instead of the If....type =1 it might be better
to stick with a simple Select Case, with one case for Boolean, one
case for String, and one case for all others.

Cheers

The Frog