Page 1 of 1

locking tables in MS Access

Posted: Thu Jun 12, 2003 9:00 pm
by discobean
I know you can't lock rows in access, i've read a little about locking (entire tables) using ASP, but I'm using PHP... obviously.

Anyway, I'm connecting to MS Access via ODBC..

I need to insert a row into a table, using an incrementing ID as the primary key... One problem, the 'incrementing ID' field (lets call it line_id) is not a MS Access autonumber...

So I've resorted to using a SELECT (MAX(line_id)+1) query, and then an insert... but this is a horrible way of doing it... As I'd imagine one day two queries will run at the same time... resulting in an incredible problem.
And I won't go live the way it is.

I dunno if MS Access does subqueries or the like, as an INSERT with a nested SELECT MAX might solve the problem, but I also need to retreive and use the newly created 'line_id' in another table.

Another thing, I can't actually change the MS Access tables at all.. so that is out of the question...

Anybody ever solved a similar problem?

Posted: Fri Jun 13, 2003 5:23 am
by discobean
Damn, it seems this one is another task for some great old aussie ingenuity...

Posted: Fri Jun 13, 2003 5:31 am
by cactus
Have you sorted it then ?

Regards,

Posted: Fri Jun 13, 2003 5:50 am
by discobean
Yes, But I'm not saying how, as it will incriminate me. lol. Dodgy brothers!

Posted: Fri Jun 13, 2003 5:59 pm
by Paddy
Hehe, the Aussie way. :)