locking tables in MS Access

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
discobean
Forum Commoner
Posts: 49
Joined: Sun May 18, 2003 9:06 pm
Location: Sydney, Australia
Contact:

locking tables in MS Access

Post 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?
User avatar
discobean
Forum Commoner
Posts: 49
Joined: Sun May 18, 2003 9:06 pm
Location: Sydney, Australia
Contact:

Post by discobean »

Damn, it seems this one is another task for some great old aussie ingenuity...
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Have you sorted it then ?

Regards,
User avatar
discobean
Forum Commoner
Posts: 49
Joined: Sun May 18, 2003 9:06 pm
Location: Sydney, Australia
Contact:

Post by discobean »

Yes, But I'm not saying how, as it will incriminate me. lol. Dodgy brothers!
Paddy
Forum Contributor
Posts: 244
Joined: Wed Jun 11, 2003 8:16 pm
Location: Hobart, Tas, Aussie
Contact:

Post by Paddy »

Hehe, the Aussie way. :)
Post Reply