Record level locking in SQL table

[Cloonalt]

My company has a large SQL database with a VB front end. They don't own the VB code and don't want to put any more investment into the current VB application as it's life cycle is coming to an end.

However..
We want to be able to lock specific records from editing and there's no way to do it with the existing front end.

Actually, what we want to do is write a front end that will allow a user to call up a specific record and lock/unlock it.

Does this sound do-able?

Any help is appreciated.

 

[ArtieChoke]

If you are using VB and ADO, you can open a recordset with pessimistic locking, which will keep others from accessing that record until you are done with it.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[Cloonalt]

Thanks, yes we can do that. The issue is to keep certain records locked for an extended period of time. Like more than one day. Does it make sense to open records and leave them open? Will the connection time out and let go of them?

Is there a REAL way to do this on the SQL side since we don't have the code for the front end (I don't even know if it's got a middle tier).

Thanks.

 

[stevexff]

Typcally database locking is an operational necessity, aimed at preventing concurrency errors. As such, it's designed to keep the lock for as short a time as possible. It's not really designed to lock them for extended periods. Consider putting a bit column on the table, and maybe using a view to return only 'unlocked' rows.

 

[Cloonalt]

Thanks, you answer is what I expected. Just thought I'd throw the question out.

We have no way for the front end to look at a view, since we don't have the front end application.