[AccessD] Writing to (editing) linked MySQL tables

[John Colby]

When using the code below to try to do an update to a field in the first
record, I get an error

Error 3146 (ODBC--call failed.) in procedure Advisor Notes
Administration.basODBCRelink.fEditODBCTable, line 0.

Function fEditODBCTable()
    On Error GoTo fEditODBCTable_Error
Dim db As DAO.Database
Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("company", , dbConsistent)

    With rst
        .MoveLast
        .MoveFirst
        .Edit
        .Fields("AddressLine2").Value = "Hi there"
        .Update
    End With


On Sun, May 15, 2022 at 6:28 PM John Colby <jwcolby at gmail.com> wrote:

> I am working on an administration app which needs to write data to a MySQL
> database on a godaddy web server.  This db holds 5 tables, of which one
> specific table needs to be updated.  The records in the tables are created
> while taking orders for our product from online forms.
>
> I have the tables linked to my Access Admin database using an ODBC driver
> I downloaded from the MySQL web site, and a connection string I found there
> as well.
>
> "ODBC;DRIVER={MySQL ODBC 8.0 Unicode
> Driver};DFLT_BIGINT_BIND_STR=1;COLUMN_SIZE_S32=1;PORT=3306;DATABASE=advisornotes;SERVER=
> SomeRandomString.secureserver.net;User=SomeUser;Password=SomePW;"
>
> If I open the tables and try to update a field in a random record I get a
> long error string.  Just as an aside, if I try to update the original
> connection string which did not have the username and password to the same
> exact thing except to have the username and password, the attempt to update
> the connection string  meets a similar fate.
>
> [MySQL][ODBC8.0(w) Driver][mysqlid-5.5.51-38.1-log]Date overflow(#0)
>
> With an OK or help button.  If I click the help button I get taken to a
> microsoft web page
>
>
> https://docs.microsoft.com/en-us/office/vba/access/concepts/miscellaneous/odbccall-failederror-3146?f1url=%3FappId%3DDev11IDEF1%26l%3Den-US%26k%3Dk(jeterr40.chm5003146)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue
>
> ODBC call failed. (error 3146)
>
> Does anyone have any expertise with this stuff?  I really don't need to
> do  manual update, and I will try it programmatically soon but I thought
> I'd throw this out there for folks to chime in if you have any info on this
> stuff.
>
> And no, I can't move to a SQ: Server db at least at this point in time.
> Eventually I fully expect to do so.
>
> Any help is much appreciated.
> --
> John W. Colby
> Colby Consulting
>


-- 
John W. Colby
Colby Consulting