[thelist] Max(ID) bad, what good?

[Warden, Matt]

> For an Access DB, I read that if you insert a new row in to a table
> and then immediately follow that with another SQL statement to SELECT
> Max(ID) you're not always going to get the ID of the record you want
> ...
>
> True or not?
>
> What would you do?

Theoretically, yes. If:

Client A inserts row ID 100

Client B inserts row ID 101

Client A selects max(ID), get's row ID 101

Client B selects max(ID), get's row ID 101

BUT, you can limit this possibility by adding a where clause of the data you
just inserted. Like so:

Client A: insert ... (fname, lname) values ('Dan', "Isdaman');

Client B: insert ... (fname, lname) values ('Joe', 'Blow');

Client A: select max(ID) ... where fname='Dan' and lname='Isdaman';

Client B: select max(ID) ... where fname='Joe' and lname='Blow';

Clients A and B will get the correct ID returned. This still isn't the best
way to do things, because it is still remotely possible to get the wrong ID
returned (assuming fname and lname don't have to be unique... and if they do,
you don't really need the ID).


--
mattwarden
mattwarden.com