MS Access + VB + ADO + Locking Records

[dbelley_office]

Hi !

I want to use an Access database for my VB 6.0 multi-user app.

Here is the big picture of my problem !!

This is my connection to an Access database.

Dim conAccess As ADODB.Connection
Dim strConnectionString as String

Set conAccess = New ADODB.Connection

strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\TestDB.mdb;"

conAccess.Open strConnectionString

This is the way I open a recordset

Dim rstRecord as ADODB.Recordset
Dim strSQL as String

Set rstRecord = New ADODB.Recordset

strSQL = "SELECT * FROM MYTABLE WHERE ..MYCONDITIONS.."  '( lets say it returns 3 records )

rstRecord.Open strSQL, conAccess, adOpenForwardOnly, adLockPessimistic

'Now .. i need to lock those records until that recordset is closed.
'The only way I found to do that is this line:
rstRecord(0) = rstRecord(0)
'It is kind of an Edit
'rstRecord.CancelUpdate allows me to unlock the data (see update below to view how I use it)

The problem with that is my whole table is locked and not only the 3 records selected. How can I have only my selected records (my recordset) locked.

Also, I don't feel I am doing this the best way. I feel my Edit technique is a turn around and not the proper way to do it.

And my updates are made this way.

Dim strSQL as String

strSQL = "UPDATE TABLE MYTABLE SET FIELD = VALUE, FIELD1 = VALUE1, FIELD2 = VALUE2 WHERE MY CONDITIONS ..."

'Unlock and Update
rstRecord.CancelUpdate
conAccess.Execute strSQL

Please help me !!
Don't send me MSDN text explaining Lock Types and blabla... I read that stuff already.

I need code exemple or hints that YOU used and that worked.

[Ecniv]

Hi,

What are you trying to do?

Are you trying to lock the selected records against update from other ppl? If so why not use a flag field, which if true means its locked.

Is it locking immediately upon connection? If so, you need to add to the connection string, something like "Shared:=Deny none" ... link on the signiture of my post to see connection strings... Able computing I think it was.


Can you please explain exactly what you are trying to achieve?


Vince

[dbelley_office]

Here is what I want to make.

USER1 enters SCREEN1.
In a textbox, he enters 0001.
A SELECT is made in TABLE1 for all records refering to that ID(0001).
At that moment, I want all the records selected to be LOCKED !!

So if USER2 enters SCREEN1 on his PC, and enters 0001. He will get an error because USER1 is already processing that ID and the rows are locked. So USER2 will only be able to see data on screen but not modify it. So I put my SAVE button Enabled=False.

When USER1 leaves SCREEN1. Then the recordset of the initial SELECT is closed and the rows in the table are unlocked and available for all other users.

---
Hope it is clearer.

I want to lock records on the SELECT for as long as the recordset remains open.

No it is not locking upon connection. The connection is initialized when the application launch and is closed the the user quits the app.

And I want to lock ONLY selected records, not the whole table and not a page of the table.

[Ecniv]

In that case it has to be a flag (field) to say whether its open or not. The updating of the record to locked should be pretty quick, but you should run some tests.

I suggest that if you do use this, that you store the user id as well, so if something untoward happens (power failure) you can identify who it was who was looking/editting the screen and left it locked. The that person can go back in (check for the same user id) and unlock it. Also you can use the username in the message that it is locked, for the users to pass on work etc between themselves.


Vince

[dbelley_office]

Is that kind of flag field in a table commonly used in multi user applications ? .. or it is some kind of turn around.

The thing is that I have relation between my database tables. And it becomes a lot of work to flag all record of all tables in relation with the ID.

There must be a way to have all that locked automatically.