How to obtain value of auto_increment field in Access linked to MySQL?

Question

I'm trying to modify and existing Access application to use MySQL as a database via ODBC with the minimal amount of recoding.

The current code will often insert a new record using DAO then obtain the ID by using LastModified. This doesn't work with MySQL. Instead I'm trying to use the approach using

SELECT * FROM tbl_name WHERE auto_col IS NULL

Suggested for Access in the MySQL documentation. However if I set up a sample table consisting of just an id and text data field and execute this

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 is returned as null. However if I execute

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

using a direct MySQL editor then id is returned correctly, so my database and approach is fine but my implementation inside Access must be incorrect. Frustratingly the MySQL documentation gives the SQL statement to retrieve the id as an example that works in Access (as it states LAST_INSERT_ID() doesn't) but gives no further details.

How might I fix this?

Answer 1

Solved (and blogged) as below

I've been implementing an upgrade for a set of Access databases to replace the Access file database with MySQL linked by ODBC. Everything seems to be going remarkably smoothly except for the common concept of inserting a record into a table with an auto-increment id column and retrieving the value of the id just created. Of course on PHP or the like one would just use the

SELECT LAST_INSERT_ID()

Function to retrieve the ID. However the MySQL documentation itself says that this doesn't work for certain ODBC applications such as Delphi or Access and suggests using

SELECT * FROM tbl WHERE auto IS NULL;

Unfortunately this simply didn't work for me when called from inside the Access application I was working with, and there seems to be several comments around the web that indeed this is unreliable as Access may drop the data connection and reconnect behind the scenes - thus invalidating the call.

As an alternative I decided to use a MySQL function to add a blank record to the table, returning the id which Access would then use to update the record (which fits well with the code-style of the existing application). Unfortunately this apparently straightforward work-around fails to be simple either as long-standing bugs in MySQL make finding valid code that can both send and return a variable something of a challenge. Several examples on the web will work within the limited domain of using either just IN or OUT variables but fail to work with both.

My final solution, which works on the MySQL 5.1 and Access 2003 combination I am deploying, is as follows

MySQL procedure

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$$

This procedure is useful in that it will insert a row and return the id for any table where a row contains all null fields or non-null fields with defaults defined. To call this I use the following function:

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

This code is somewhat unusual in that normally, on MSSQL, you would use a parametrized procedure call, but due to bugs in the MySQL ODBC (or at least incompatibilities with Access) the above seems to be the only way that allows both data to be passed and returned.