mysql - DAO.Recordset MySQL 后端在字符串周围强制使用单引号

Question

我的链接表给我一个错误“3197”。似乎以下失败并显示“3197”:

.edit
!some_field = "Barbara"
.update

以前不是这样的。似乎我可以解决这个问题的唯一方法是忽略错误——因为除了得到错误之外一切正常——或者这样插入单引号:

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

我可以做到这一点，但它很容易出错。我有数百行代码必须更改，而且在阅读表格时我必须去掉单引号。我将所有内容存储为字符串。

我已经尝试了访问中的记录锁和记录集创建中的记录锁的所有修复。我已经确保服务器上的每个表都有默认条目，并且有一个主键。基本示例是我现在所在的位置。我正在使用一张特定的表，其中包含一个特定的条目。如果我把它放在单引号中，它就可以工作。

[根据要求编辑]

[表创建]

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

[当前函数]

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

正如@ComputerVersteher 用果断的英语指出的那样:

我的问题的答案很简单。 MySQL 要求所有要更新的表都包含一个时间戳字段，默认=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。该要求的记录很少，并且具有选择性。如果您在编辑表格时也遇到了问题，这可以很好地解决这个问题。它适合我。

下面的文档明确表达了这一点，但并没有真正深入到任何细节

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

使用工作台编辑表格。在列表的底部添加一个新字段。随便命名。声明字段时间戳——删除括号。在更新 CURRENT_TIMESTAMP 时添加默认值=CURRENT_TIMESTAMP。将字段移动到列表中您想要的位置。在运行应用程序之前，请始终单击该条目。这将用创建日期/时间填充您的字段。

好了。简单的。 7 天简单。