[Vertigomf]

Hi,

Let me say first of all that I am completely new to this forum and also not a desperately experienced in ASP and VBScript programmer.

My problem is the generation of a truly unique ID for a record in an access DB (please don't start telling me to use a 'real' database - that's what I've got and that's what I have to use!). Yes, I know there are loads of discussions out there on this topic but none of them are completely satisfactory.

Here are my thoughts as I understand things and the problems with them:

The simplest approach is to use an autonumber - fine this gives a unique ID, but my problem is that I need to copy these records from one db to another (identical structure) and they MUST retain the same ID after the copy. This is not an option with autonumber.

Next we have max approach as in:

Code:

      rsNewPacks_cmd.CommandText = "SELECT max(pkPackID) AS maxid FROM tblPacks WHERE pkEventID=" + CStr(newEventID)
      Set rsNewPacks = rsNewPacks_cmd.Execute
      newPackID = rsNewPacks("maxid")

Now I can go ot to use the newPackID in a new record. I have seen this proposed many times in discussions throughout the web and indeed used in some of our own legacy code and quite frankly I'm horrified. Maybe I'm misunderstanding it, but it seems to be that the internet is pretty much the biggest multiuser environment in the world and sooner or later two users will access the database at approximately the same time both getting the same maximum value before the other has the chance to save their new record. Result - two (or more if we are really busy) records with identical IDs.

I have considered using a random number and keep generating a new one until we get one that doesn't yet exist. Not as horrendous as it might seem if (a) we are not doing this too often and (b) the table in question is not too big. Unfortunately whilst I could go down this route successfully it is really going to be much more convenient to the user if the generated IDs are (more or less) sequential - which pushes me back towards the autonumber arghhhh.

Can anyone out there help? pretty please!

Mike

[Vertigomf]

Well I have to say I'm a little disappointed by the lack of response to my first post but maybe there's less interest in plain vanilla ASP now. So I shall stick my neck out and post the solution I have come up with and see what people think.

Basically I want to generate a unique record ID in a multi user environment without using autonumber (as discussed in the original post).

The solution I have come with is to use the max approach as shown above but put it in a loop checking for an id number clash. At first glance it seems inefficient but then most of the time there will not be a multi user clash and the first attempt should work fine. So here it is:

First make sure the field is set to indexing with no duplicates allowed
use something like:

Code:

cmd.CommandText = "SELECT max(oOrderID) AS maxid FROM tblOrders"
Set rs = cmd.Execute
Dim orderid, done
if IsNull(rs("maxid")) Then orderid = 1 Else orderid = rs("maxid") + 1
done = false
' we have set up a number field set to 'no duplicates'. 
' Then we will work out the next logical id (in the code just above).
' however since the internet is a major multiuser system we have to 
' assume that it is likely for another user to come in at the same time
' and end up with the exact same id. So we will try to save the record 
' with that id and if it fails increment it by one and try again until it succeeds

cmd.CommandText = "INSERT INTO tblOrders (oOrderID, ........) VALUES (?, ...........)" 
cmd.Parameters.Append cmd.CreateParameter("param0", 3, 1, -1, orderid)
..............

on error resume next
While Not done
  Err.Clear
  cmd.Parameters.Item(0).Value = orderid
  cmd.Execute		  
  If Err.Number <> 0 Then  ' we will assume that it has failed due to an id number clash
    orderid = orderid + 1
   Else
    done = true
  End If
Wend
on error goto 0

I have left much of the code out only putting in the relevant bits!

Maybe someone would like to comment on this solution, in particular the commented assumption about the error trapping. Maybe offer a better solution?

regards
Mike

[wirehopper]

How To Use GUIDs w/ Access, SQL 6.5 and SQL 7

[Vertigomf]

Thanks for that wirehopper, however whilst I may have misunderstood - that article seems to suggest that the Access GUID is still essentially an autonumber which would mean that I would not be able to explicitly copy an existing one into the field. This is essentially my primary requirement. I need a unique ID that can be overwritten with a different value, which autonumbers do not permit. It looks to me like the GUID would suffer from the same problem.

Thanks
Mike

[wirehopper]

I think it's worth a try - if the database is set up to allow the key to be a string, rather than an autoincrement, you should be able to create and update with the GUIDs as they described it.

[Vertigomf]

Thanks for that again Wirehopper, I'll try and give that a go sometime. I've got the other mechanism working now and as always tends to be the case I have such a tight deadline that I'm going to have to live with that for now. My other slight concern, as the table in question has relatively few fields in each record but does have a LOT of records, is that a GUID is pretty big (in this case almost as big as the rest of the fields put together) and so the overhead is pretty high. That said I will have to take a look at it and see what happens.

Mike