How to obtain value of auto_increment field in Access linked to MySQL?
- by Cruachan
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?
