[AccessD] Writing to (editing) linked MySQL tables

[Stuart McLachlan]

I don't think dbConsistent is valid with an ODBC record source 

"Microsoft Jet dynaset-type and snapshot-type Recordset objects only"

On 15 May 2022 at 18:42, John Colby wrote:

> 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/miscella
> > neous/odbccall-failederror-3146?f1url=%3FappId%3DDev11IDEF1%26l%3Den
> > -US%26k%3Dk(jeterr40.chm5003146)%3Bk(TargetFrameworkMoniker-Office.V
> > ersion%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
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>