Last ID

[Acropolis]

hi guys,


I have just moved by Access BE across to MySQL BE at the insistence of the IT dept, and am having a couple of problems with things.


One of these been getting the ID last a record that has just been inserted. I used to get this no problem when it was an Access BE by using .bookmark and .lastmodified but these don't work with MySQL.


I have spent some time searching and found various methods but cant get any of them to work.


The records is inserting absolutely fine, but I cant get the ID back. Any idea's?


the below is just a test section of code to try and get it working.


thanks

Private Sub Command0_Click()
Dim db As DAO.Database
Dim IDI As Integer
Dim query As String
Dim rs1 As DAO.Recordset

Set db = CurrentDb
db.Execute "INSERT INTO SB_Test (TestText) VALUES ('Worked')"
Set rs1 = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
IDI = rs1!LastID
rs1.Close
Debug.Print IDI
Set rs1 = Nothing
Set db = Nothing

End Sub

 

[pr2-eugin]

The method you have used (@@Identity) should work. Is it not?

 

[Acropolis]

Nope, just returns a value of 0 every time.

 

[pr2-eugin]

Since it is a Linked table to MySQL, you need to create a Pass through Query using

SELECT LAST_INSERT_ID();

This should get you the ID.

 

[Acropolis]

Yeah just came to the same conclusion, trying to suss it out now. Thanks

 

[Acropolis]

Nope its got me beat. I have created a pass through query in the query designer and it returns the value I am looking for.


But I have absolutely no clue how to translate this into VBA/Get the result from this query into VBA.


Any feel like helping me out here please?

 

[CJ_London]

here is a link which has a couple of solutions

http://www.dbforums.com/microsoft-access/1006185-writing-pass-through-queries-vba.html

 

[Acropolis]

right I have got this far, but I am still getting back just a 0 value every time, running the query in access works fine, but nothing here

Private Sub Command0_Click()

CurrentDb.Execute "INSERT INTO SB_Test (TestText) VALUES ('SOMETHIGN ELSE')"
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = CurrentDb.CreateQueryDef("")
qdf.Connect = "ODBC; Driver={MySQL ODBC 5.2 UNICODE Driver};Server=#####;Database=#####;User=####;Password=####;Option=3;"
qdf.SQL = "SELECT LAST_INSERT_ID() AS LastID;"
qdf.ReturnsRecords = True
Set rs = qdf.OpenRecordset
Debug.Print rs("LastID")
rs.Close
Set rs = Nothing
Set qdf = Nothing
End Sub

 

[CJ_London]

One problem is that currentdb will always return zero, you need to assign it to another db object as per the example i.e.

dim db as dao.database
 
set db=currentdb
db.execute(.....
set qdf=db.createquerydef(....

Also not sure you can alias last_insert_id, suggest you remove this until you know it is working

 

[Acropolis]

Is this what you mean?

Private Sub Command0_Click()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "INSERT INTO SB_Test (TestText) VALUES ('SOMETHIGN ELSE123')"
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Set qdf = db.CreateQueryDef("")
qdf.Connect = "ODBC; Driver={MySQL ODBC 5.2 UNICODE Driver};Server=###;Database=###;User=###;Password=###;Option=3;"
qdf.SQL = "SELECT LAST_INSERT_ID();"
qdf.ReturnsRecords = True
Set rs = qdf.OpenRecordset
Debug.Print rs("LAST_INSERT_ID()")
rs.Close
Set rs = Nothing
Set qdf = Nothing
End Sub

 

[Acropolis]

After some messing around I finally got it working.


Don't know what was wrong exactly, but something to do with the connection string, I don't think its the SQL and heres why.


What I have done is create a pass through qry and save it within access, and referenced that instead of the connection string and SQL and it works an absolute treat first time everytime.


The SQL in that qry is identical (copied and pasted) to the code, so it must be something to do with the connection string.