Access rror updating a field in a table mYSql

[Welshgasman]

Forum working fine for me?


You can mark the thread solved then? Top right of thread, Thread tools.

[roberto21]

I have no doubt that the forum is working fine for you. It's me that is kicked out (three times this time) Now I write the post in Notepad, and then copy it as fast as I can, before getting kicked out. OK, no problem.
The thread is closed. Beware; with sql9.3 you MUST use dbSeeChanges. If you use dbFailOnError you will get the error. Is that correct that you cannot use more than one option in database.execute statement? Microsoft is not clear on this point, and writes "db.excute query, optionS" (plural).

[Welshgasman]

It's me that is kicked out (three times this time)

The first thing I would do in that case then, is try another browser.

I use Chrome mainly.

[Minty]

I have no doubt that the forum is working fine for you. It's me that is kicked out (three times this time) Now I write the post in Notepad, and then copy it as fast as I can, before getting kicked out. OK, no problem.
The thread is closed. Beware; with sql9.3 you MUST use dbSeeChanges. If you use dbFailOnError you will get the error. Is that correct that you cannot use more than one option in database.execute statement? Microsoft is not clear on this point, and writes "db.excute query, optionS" (plural).

The correct way to use both :

db.Execute strSQL, dbFailOnError + dbSeeChanges

They are bitwise options so adding them together Access know to use both.

[roberto21]

Thank you for the info. You are right, using that way you don't get the compile error. However, writing

db.Execute strSQL, dbFailOnError + dbSeeChanges
or
db.Execute strSQL, dbSeeChanges + dbFailOnError

causes the dreaded error (access blocked etc etc). Using only dbSeeChanges works fine. I am not sure I understand what is going on here, because if the error is in the lack of dbSeeChanges the previous statements should work. Any ideas?

[roberto21]

I don't know if someone is still interested in the matter, but here there are two more pieces in the puzzle.
1) using the Recordset technique (see my first post) does not work in any case. Even removing dbFailOnError and leaving dbSeeChanges, the error pops out anyhow.
2) the following technique (execute querydef) works OK even with dbFailOnError

Private Sub AggiornaLibroSoci(NTes As Integer, nDate As Date)
    Dim dbs As DAO.Database
    Dim rstLibroSoci As DAO.Recordset
    Dim strSql As String
    Dim nYear As Integer
    Dim qdefUpdate As QueryDef
        
    nYear = Year(Date)
    If Month(nDate) > 8 Then
        nYear = nYear + 1
    End If
    
    Set dbs = CurrentDb
   
    If Me.Nuova_TessElett <> "" Then
        strSql = "Update LibroSoci Set [Quota associativa] = " & nYear & _
            ", [Tessera Elettronica] = " & Me.Nuova_TessElett & " WHERE [Numero Tessera] = " & NTes
    Else
        strSql = "Update LibroSoci Set [Quota associativa] = " & nYear & " WHERE [Numero Tessera] = " & NTes
    End If
    
    Set qdefUpdate = dbs.CreateQueryDef("", strSql)


    qdefUpdate.Execute dbFailOnError



    Set dbs = Nothing

[Gicu]

I did ask you what options do you use in your connection string but you never answered; it might be that your connection is not set up properly for use with and Access front-end. I am no MySQL expert but many years ago when I was heavily involved with Access db development there were some options you needed to use in your ODBC driver (I believe at the time I was using 5.1) for the MySQL back-end to work with the Access front-end.
Here is an example of what I mean, this is from the msysObjects system table's Connect field for the linked MySQL table:
DRIVER={MySQL ODBC 5.1 Driver};SERVER=MyServer;DATABASE=MyDB;PORT=3306;UI D=JohnDoe;PWD=SecretPW;Option=4194314

Here is a page I found with some more info:
https://dev.mysql.com/doc/connector-...n-option-flags

Cheers,

[roberto21]

Sorry for the missed answer. The connection string I use

DRIVER={MySQL ODBC 9.4 Unicode Driver};UID=root;PWD=rootpwd;DATABASE=nuovodbricev utemysql; DFLT_BIGINT_BIND_STR=1;NO_SCHEMA=1;PORT=3306;SERVE R=localhost;ACCDB=YES;

I have no idea about the OPTION parameter. You think is important (boldface), but really I don't know what to make of it. Tried to read the documentation, but it is still unclear. I plead guilty of ignorance

[Gicu]

I would at least add the two options\parameters (FOUND_ROWS=1;DYNAMIC_CURSOR=1) as suggested in the link I've sent you:

Cheers,

[roberto21]

Thank you very much for your suggestions. I ran a few tests adding those parameters to the connection string, that now reads


DRIVER={MySQL ODBC 9.4 Unicode Driver};UID=root;PWD=mysql1023root;DATABASE=nuovod bricevutemysql;DFLT_BIGINT_BIND_STR=1;NO_SCHEMA=1; PORT=3306;SERVER=localhost;ACCDB=YES;FOUND_ROWS=1; DYNAMIC_CURSOR=1;


asnd indeed something changed. Now the ,execute technique works also adding dbFailOnError

With dbs
        .Execute strSql, dbSeeChanges + dbFailOnError
        MsgBox .RecordsAffected & " LibroSoci record(s) updated"
 End With

and also the technique with the recordset now works, even though it does not accept the dbFailOnError (according to Ms, it should: is included in the optionEnum list, but maybe I misinterpreted)

 Set rstLibroSoci = dbs.OpenRecordset(strSql, dbOpenDynaset, dbSeeChanges)


    rstLibroSoci.Edit


        rstLibroSoci![Quota associativa] = nYear
        If Me.Nuova_TessElett <> "" Then rstLibroSoci![Tessera Elettronica] = Me.Nuova_TessElett




   rstLibroSoci.Update

Thanks again for your time and patience. There is really a lot to learn and understand.

[Gicu]