DAO Recordsets

[EKelly]

Hi guys,
I have searched through a lot of previous posts for an answer but can't seem to find what im looking for.
I am working on an existing application that is accessing an Access DB through VB with DAO. When the application starts the first thing it does is to open every Access table via recordsets (upwards of 40 tables). This application is now going to be used by more than one user and i am trying to implemet record locking. What I need to know is if there is a way of locking a recordset after it has been opened or else is there a specific way of locking at the same time as opening. Idealy what I would like to do is when a form opens, lock every recordset that that form uses and unlock when the form closes. I realise that this is a pretty broad question but i can't seem to find a straight "How to" on the subject. Has anyone got some words of wisdom or can anyone point me where i will find some!

Thanks in advance
Erin

 

[ZmrAbdulla]

I am no expert on this..
May be a starting point to search on topic "OpenRecordset Method" in the VBA help file

________________________________________
Zameer Abdulla
Visit Me
The best thing to spend on your child is your time.

 

[RPOC]

Try doing searches on pessimistic and opportunistic record locking. Also be aware of the earlier versions of Access, they lock by memory blocks not by individual records.

 

[itmasterw]

You need to use ADO and then you can have more controll over locking. While DAO had some it was very limited. You will need to look upi the different arguments you can have here, but here is an example of using ADO with locking atribute.

Dim Rs As ADODB.Recordset

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data SOURCE = C:\tempFiles\Sales.mdb;"
'Cn.ConnectionTimeout = 30
Cn.Open

Set Rs = New ADODB.Recordset

Rs.Open ("Select [30 Days] FROM North_Sales"), _
Cn, adOpenKeyset, adLockOptimistic

The adLockOptimistic is the atribute that gives you this controll, but there are otheers and depending on what you want will depend on which one you use.


ITM