代码之家 › 专栏 › 技术社区 › Cruachan

如何获取mysql链接的access中auto-increment字段的值?

ms-access mysql

Cruachan · 技术社区 · 14 年前

我正在尝试修改和现有的Access应用程序,通过ODBC使用MySQL作为数据库,只需最少的重新编码。

当前代码通常使用DAO插入新记录,然后使用lastmodified获取ID。这不适用于MySQL。相反,我尝试使用

SELECT * FROM tbl_name WHERE auto_col IS NULL

建议访问 MySQL documentation . 但是,如果我设置了一个仅包含ID和文本数据字段的示例表,并执行此操作

CurrentDb.Execute ("INSERT INTO tbl_scratch (DATA) VALUES ('X')")
Set rst = CurrentDb.OpenRecordset("SELECT id FROM tbl_scratch WHERE id IS NULL")
myid = rst!id

ID返回为空。但是如果我执行

INSERT INTO tbl_scratch (DATA) VALUES ('X');
SELECT id FROM tbl_scratch WHERE id IS NULL;

使用一个直接的mysql编辑器,然后正确地返回id,所以我的数据库和方法很好,但是我在access中的实现一定是不正确的。令人沮丧的是,MySQL文档给出了一个SQL语句来检索ID,作为一个在Access中工作的示例(正如它所说的最后一个插入的ID()没有),但没有给出进一步的细节。

我该怎么解决这个问题?

1 回复 | 直到 14 年前

Cruachan 14 年前

解决(和博客)如下

我一直在为一组Access数据库实现升级,用由ODBC链接的MySQL替换Access文件数据库。除了将记录插入具有自动递增ID列的表中并检索刚刚创建的ID的值这一常见概念外,一切似乎都非常顺利。当然,在PHP或类似的应用程序中,只需使用

选择最后一个插入ID()。

函数来检索ID。但是,MySQL文档本身表示,这对某些ODBC应用程序(如Delphi或Access)不起作用,建议使用

从tbl中选择*,其中auto为空;

不幸的是,当从我正在使用的访问应用程序内部调用时,这对我来说根本不起作用,而且在Web上似乎有一些评论认为这确实不可靠,因为访问可能会中断数据连接并在后台重新连接,从而使调用无效。

作为另一种选择,我决定使用MySQL函数向表中添加一个空白记录,返回Access随后用于更新记录的ID(这与现有应用程序的代码样式非常匹配)。不幸的是,这种明显简单的解决方法并不简单,因为MySQL中的长期错误使得找到既能发送又能返回变量的有效代码成为了一种挑战。Web上的几个示例将在有限的领域内工作,即只使用输入或输出变量,但无法同时使用这两个变量。

我的最后一个解决方案是,它适用于我正在部署的MySQL5.1和Access2003组合,如下所示

MySQL过程

DELIMITER $$

CREATE
    PROCEDURE `alicedata`.`sp_ins_identity`(IN tablename VARCHAR(50))
    BEGIN
    SET @result = 0;
    SET @sqlproc = CONCAT("INSERT INTO ",tablename," () VALUES ();");
    PREPARE s1 FROM @sqlproc;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;
    SELECT LAST_INSERT_ID();
    END$$

此过程非常有用,因为它将插入一行,并返回行中包含所有空字段或定义了默认值的非空字段的任何表的ID。要调用此函数,我使用以下函数:

Public Function InsertMySQLIdentityRow(DSN As String, Tablename As String) As Integer
On Error GoTo Err_InsertMySQLIdentity

Dim cnnSQL As New ADODB.Connection
Dim cmdSQL As ADODB.Command
Dim pid As Integer
Dim rs
Dim strSQL As String

    ' initialize
    pid = 0

    ' set up ADO connection
    Set cnnSQL = New ADODB.Connection
    cnnSQL.Open DSN

    ' execute the procedure - note that assembling by parameter fails to handle IN or OUT correctly

    Set cmdSQL = New ADODB.Command
    cmdSQL.ActiveConnection = cnnSQL

    strSQL = "call sp_ins_identity('" & Tablename & "');"
    Set rs = CreateObject("ADODB.Recordset")
    Set rs = cnnSQL.Execute(strSQL)

    If Not rs.EOF Then
      pid = rs(0)
    End If

    ' clean up
    Set rs = Nothing
    Set cmdSQL = Nothing
    cnnSQL.Close
    Set cnnSQL = Nothing

Exit_InsertMySQLIdentity:
    InsertMySQLIdentityRow = pid
    Exit Function

Err_InsertMySQLIdentity:
    MsgBox Err.Number & Err.Description
    Resume Exit_InsertMySQLIdentity
End Function

这段代码有点不寻常,因为在MSSQL上,您通常会使用参数化过程调用,但由于MySQL ODBC中存在错误(或至少与Access不兼容),以上似乎是允许传递和返回这两个数据的唯一方法。