[AccessD] Writing to (editing) linked MySQL tables

[John Colby]

Well if you don't pass that parameter it defaults to dynamic which fails in
the same manner.

On Sun, May 15, 2022, 10:49 PM Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> 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
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>