ADO Query based on MariaDB

[Adelina_RO]

For the last 17 years i've been using DAO recordsets and i LOVE them, but recently i got a new project which has the BE on a MariaDB server. All fine and dandy until this issue:
I have a simple "Select * FROM" passtrough
query which doesn't see changes made to the underlying table. I've tried Opions: 2, 3, 32... to noavail. Now i see that if i make any changes to the table structure directly on the mariadb server, until i quit access, the simple query becomes dynamic and sees changes in real time, but as soon as i reopen access, the same query reverts to whatever it is and needs me to press the F5 button to see any changes. Why? Any ideas?

L.E. If i open the query in access query editor and change the Options part of the conn string, run it, press F5 then it becomes updatable again until i close it. Then it reverts...

L.E.2 The query becomes not updatable as soon as i save it and close it

 

[Adelina_RO]

I've made this simpler and still nothing:

Private Sub Form_Load()
Dim qc As DAO.QueryDef
Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM Locked", SQL_SVN, adOpenDynamic

DoCmd.DeleteObject acQuery, "QQQQ"

Set qc = CurrentDb.CreateQueryDef("QQQQ")
    qc.Connect = "ODBC;DRIVER={MySQL ODBC 8.0 ANSI Driver};UID=Admin;PWD=xxxxx;Server=192.168.1.250;port=3309;Database=xxxxx;Option=32;"
    qc.SQL = "SELECT * FROM Locked"
    qc.ReturnsRecords = True
    qc.Close
    
Me.RecordSource = "QQQQ"
End Sub

It seems that any Option I use ends up the same: recordset is not updatable...

 

[arnelgp]

have you tried to use a simple Linked table instead of passthrough query.

 

[Adelina_RO]

i have. and it works. but what i needed was a big query with around 30 fields and over 6k rows, joined form 15 tables or so...
i wanted to have a continuous form, shared by multiple users, and when someone started editing on of the rows (in a separate form) the other users would know by use of a checkbox (true/false). it works as it is, but i need to issue a Me.recordset.requery. what i wanted was to see the updated checkbox realtime but, after some research, i gave that dream up. thanks anyway