DAO.Recordset MySQL Backend Forcing Single Quotation Marks Around Strings

Question

I have linked tables that are giving me an error "3197". It seems that the following fails with a "3197":

.edit
!some_field = "Barbara"
.update

it was not always like this. The only way it seems i can hadle this is by either ignoring the error--because everything works beyond getting the error--or by inserting single quotations as so:

.edit
!some_field = "'Barbara'"
.update

i can do this, but it would be error prone. i have hundreds of lines of code i'd have to change, and i would have to take out the single quotations when reading the table. i store everything as a string.

i have tried every fix from recordlocks in access, and recordlocks in the recordset creation. i have made sure that every table on the server has default entries, and has a primary key. the basic example is where i'm at right now. i am using one specific table, with one specific entry. if i put it in single quotation marks it works.

[edit as per request]

[table create]

CREATE TABLE `Client Information` (
  `client_id` smallint(6) NOT NULL DEFAULT '0',
  `client_id_proper` varchar(255) DEFAULT '0000',
  `client_timestamp` timestamp(6) NULL DEFAULT NULL,
  `client_tracking` varchar(255) DEFAULT '2017-01-01',
  `client_id_code39` varchar(255) DEFAULT 'MAC CLIENT 0000',
  `client_name_first` varchar(255) DEFAULT 'unknown',
  `client_name_last` varchar(255) DEFAULT 'unknown',
  `client_name_initials` varchar(255) DEFAULT 'unknown',
  `client_name_artist` varchar(255) DEFAULT 'unknown',
  `client_date_joined` varchar(255) DEFAULT 'unknown',
  `client_role_active_0000` varchar(255) DEFAULT 'unknown',
  `client_role_active_0001` varchar(255) DEFAULT 'unknown',
  `client_role_active_0002` varchar(255) DEFAULT 'unknown',
  `client_role_active_0003` varchar(255) DEFAULT 'unknown',
  `client_phone_home` varchar(255) DEFAULT 'unknown',
  `client_phone_home_unlisted` bit(1) DEFAULT b'0',
  `client_emergency_contact` varchar(255) DEFAULT 'unknown',
  `client_emergency_relation` varchar(255) DEFAULT 'unknown',
  `client_emergency_phone` varchar(255) DEFAULT 'unknown',
  `client_emergency_phone_unlisted` bit(1) DEFAULT b'0',
  `client_phone_alternate` varchar(255) DEFAULT 'unknown',
  `client_phone_alternate_unlisted` bit(1) DEFAULT b'0',
  `client_email` varchar(255) DEFAULT 'unknown',
  `client_notes` varchar(1023) DEFAULT 'none',
  `client_id_pwd` varchar(255) DEFAULT 'macbooks',
  PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

[current function]

Option Explicit


Public Function Update_Client_Information(persistence_mode As Integer)
'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************

    Dim record_count As Integer


    Dim strSQL As String


    Dim rst_ctl As DAO.Recordset

'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************
'**************************************************************************************************

    strSQL = "SELECT [client_id]" & _
            " FROM [Client Information]" & _
            " ORDER BY [client_id] ASC"

    Set rst_ctl = dbs_mac.OpenRecordset(strSQL, Type:=dbOpenDynaset)

    If rst_ctl.BOF Eqv True Then

        [Procedures Core].EmergencyExitFunction ("unable to load the artists' records.")

        GoTo Line1

    End If

    rst_ctl.MoveLast: rst_ctl.MoveFirst


    record_count = rst_ctl.RecordCount


    If persistence_mode = 1 Then

        strSQL = _
            "SELECT *" & _
            " FROM [Client Information]" & _
            " WHERE [client_id] = " & record_count & _
            " ORDER BY [client_id] ASC"

        Set rst_ctl = dbs_mac.OpenRecordset(strSQL, Type:=dbOpenDynaset)

        If rst_ctl.BOF Eqv True Then

            [Procedures Core].EmergencyExitFunction ("unable to load the artist's record.")

            GoTo Line1

        End If

        rst_ctl.MoveLast


        record_count = record_count + 1


        rst_ctl.AddNew
        rst_ctl.Fields("client_id") = record_count
        rst_ctl.Fields("client_id_proper") = [Procedures Utility].convert_int_to_proper(record_count)
        rst_ctl.Fields("client_id_code39") = MAC_CLIENT_PREFIX & [Procedures Utility].convert_int_to_proper(record_count)
        rst_ctl.Fields("client_tracking") = Year(Date) & "-" & Format(Date, "mm") & "-" & Day(Date)
        rst_ctl.Fields("client_name_first") = Forms!frm_client!frm_client_information("input_client_name_first")
        rst_ctl.Fields("client_name_last") = Forms!frm_client!frm_client_information("input_client_name_last")
        rst_ctl.Fields("client_name_initials") = Forms!frm_client!frm_client_information("input_client_name_initials")
        rst_ctl.Fields("client_name_artist") = Forms!frm_client!frm_client_information("input_client_name_artist")
        rst_ctl.Fields("client_phone_home") = Forms!frm_client!frm_client_information("input_client_phone_home")
        rst_ctl.Fields("client_phone_home_unlisted") = Forms!frm_client!frm_client_information("input_client_phone_home_unlisted")
        rst_ctl.Fields("client_phone_alternate") = Forms!frm_client!frm_client_information("input_client_phone_alternate")
        rst_ctl.Fields("client_phone_alternate_unlisted") = Forms!frm_client!frm_client_information("input_client_phone_alternate_unlisted")
        rst_ctl.Fields("client_emergency_contact") = Forms!frm_client!frm_client_information("input_client_emergency_contact")
        rst_ctl.Fields("client_emergency_relation") = Forms!frm_client!frm_client_information("input_client_emergency_relation")
        rst_ctl.Fields("client_emergency_phone") = Forms!frm_client!frm_client_information("input_client_emergency_phone")
        rst_ctl.Fields("client_emergency_phone_unlisted") = Forms!frm_client!frm_client_information("input_client_emergency_phone_unlisted")
        rst_ctl.Fields("client_date_joined") = Forms!frm_client!frm_client_information("input_client_date_joined")
        rst_ctl.Fields("client_role_active_0000") = Forms!frm_client!frm_client_information("input_client_role_active_0000")
        rst_ctl.Fields("client_role_active_0001") = Forms!frm_client!frm_client_information("input_client_role_active_0001")
        rst_ctl.Fields("client_role_active_0002") = Forms!frm_client!frm_client_information("input_client_role_active_0002")
        rst_ctl.Fields("client_role_active_0003") = Forms!frm_client!frm_client_information("input_client_role_active_0003")
        rst_ctl.Fields("client_email") = Forms!frm_client!frm_client_information("input_client_email")
        rst_ctl.Fields("client_notes") = Forms!frm_client!frm_client_information("input_client_notes")
        rst_ctl.Update


        [Persist Client].Add_Account_Record

        [Persist Client].Add_Volunteer_Record

        [Persist Client].Add_Rehabilitation_Fund_Record

        [Persist Client].Add_Art_Inventory_Record


    ElseIf persistence_mode = -1 Then


        strSQL = "SELECT *" & _
            " FROM [Client Information]" & _
            " WHERE [client_id] = " & Forms!frm_client!frm_client_information("input_client_id") & _
            " ORDER BY [client_id] ASC"

        Set rst_ctl = dbs_mac.OpenRecordset(strSQL, Type:=dbOpenDynaset)

        If rst_ctl.BOF Eqv True Then

            [Procedures Core].EmergencyExitFunction ("unable to load the artist's record.")

            GoTo Line1

        End If

        rst_ctl.MoveLast: rst_ctl.MoveFirst


        rst_ctl.Edit
        rst_ctl!client_name_first = Forms!frm_client!frm_client_information("input_client_name_first")
        rst_ctl!client_name_last = Forms!frm_client!frm_client_information("input_client_name_last")
        rst_ctl!client_name_initials = Forms!frm_client!frm_client_information("input_client_name_initials")
        rst_ctl!client_name_artist = Forms!frm_client!frm_client_information("input_client_name_artist")
        rst_ctl!client_phone_home = Forms!frm_client!frm_client_information("input_client_phone_home")
        rst_ctl!client_phone_home_unlisted = Forms!frm_client!frm_client_information("input_client_phone_home_unlisted")
        rst_ctl!client_phone_alternate = Forms!frm_client!frm_client_information("input_client_phone_alternate")
        rst_ctl!client_phone_alternate_unlisted = Forms!frm_client!frm_client_information("input_client_phone_alternate_unlisted")
        rst_ctl!client_emergency_contact = Forms!frm_client!frm_client_information("input_client_emergency_contact")
        rst_ctl!client_emergency_relation = Forms!frm_client!frm_client_information("input_client_emergency_relation")
        rst_ctl!client_emergency_phone = Forms!frm_client!frm_client_information("input_client_emergency_phone")
        rst_ctl!client_emergency_phone_unlisted = Forms!frm_client!frm_client_information("input_client_emergency_phone_unlisted")
        rst_ctl!client_date_joined = Forms!frm_client!frm_client_information("input_client_date_joined")
        rst_ctl!client_role_active_0000 = Forms!frm_client!frm_client_information("input_client_role_active_0000")
        rst_ctl!client_role_active_0001 = Forms!frm_client!frm_client_information("input_client_role_active_0001")
        rst_ctl!client_role_active_0002 = Forms!frm_client!frm_client_information("input_client_role_active_0002")
        rst_ctl!client_role_active_0003 = Forms!frm_client!frm_client_information("input_client_role_active_0003")
        rst_ctl!client_email = Forms!frm_client!frm_client_information("input_client_email")
        rst_ctl!client_notes = Forms!frm_client!frm_client_information("input_client_notes")
        rst_ctl.Update

    End If

Line1:

    If Not (rst_ctl Is Nothing) Then

        rst_ctl.Close

        Set rst_ctl = Nothing

    End If


End Function 

Answer 1

as @ComputerVersteher pointed out in decisive english:

the answer to my question is a simple one. MySQL requires that all tables to be updated contain a timestamp field with default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. the requirement is poorly documented and is selective in nature. if you are having troubles with editing tables as well, this could very well fix that. it has for me.

the following documentation expresses this explicitly, but doesn't really go into any detail

https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html

using workbench edit a table. at the bottom of the list add a new field. name it whatever. declare the field timestamp--DELETE THE BRACKETS. add default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. move the field to your desired place in the list. always click off of the entry before you run apply. this will fill your field with the creation date/time.

and there you have it. simple. 7days simple.