MySQL Forums
Forum List  »  Microsoft Access

Re: DAO/Access2k/MySQL5 rs.addnew #DELETED#
Posted by: Jake Yazici
Date: September 12, 2005 05:55PM

Hi Felix!

I'm glad my post helped. I had of thought of a way to reliably retrieve the last inserted record in a multi-user environment akin to your method. Instead of a "Date Entered" field, simply use a field like a GUID. This is the same spirit as preserving the value from the "Now()" function for subsequent recall of the newly inserted record. Your method uses uniqueness in the time dimesion. The GUID would be much better as it provides time and space uniqueness. The spatial uniqueness is based on the algorithm which typically uses the local client machine's MAC address (or some other random number). This means that even though two disparate users may insert a record simultaneously, the fact that they are using separate machines is enough to produce distinct keys.

Nonetheless, all of these are hackish workarounds for a shoddy ODBC driver. I am looking forward to a decent beta of Connector/ODBC 5.0. Any word on this? (Anyone? Anyone?)

As for how to get GUIDs, there are typically many GUID generator implementations available. The likelyhood of a key collision based on this is astronomically small. Heck... it could be any field type that can contain a GUID-like value (e.g. textfield), incase you don't want to get all caught up in bytearray fiascos.

Here are a couple of examples for the GUID generator:

http://www.devx.com/vb2themax/Tip/18261
http://www.mrexcel.com/tip078.shtml

As a further thought...

For sake of efficiency in batch inserts, a singe GUID value could be generated as a key at the beginning of the insert routine. This same key value could be used for inserting whole sets of records. Later, the whole batch of newly inserted records could be retrieved by filtering for records with that exact key. Once the actual primary keys are retrieved, the GUID field could be nulled out, or a new GUID key can be used for subsequent inserts.

Come to think of it, a single GUID key could be used for all inserts. Assume the PK for the tables are all called "RecId" and are of an Auto_inc integer. This would be akin to a session key and a function could be defined as such:

Function Last_Inserted_Id(tablename as String, session_id as String) as integer
{
Dim strSQL as String
Dim rst as New ADODB.RecordSet

' Retrieve last inserted auto_inc PK for given session
strSQL = "SELECT max(RecId) from " + tablename + " where session_id= '" + session_id + "';"

rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

Last_Inserted_Id = rst("RecId")

rst.Close
}


Comments are welcome.

Cheers!

-Jake

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: DAO/Access2k/MySQL5 rs.addnew #DELETED#
2709
September 12, 2005 05:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.