SQL Server locking problem

[valentindvf]

Hi,

I have a VB 6.0 application that is a front end for an SQL Server database. The problem is than if an instance of the application uses the same form that uses another instance of the same application on another computer, on instance block the other an vice versa. When I update information on the server I use transactions. I tried all the options for cnn.IsolationLevel=X, but no result. If even the aplications tries to query a table that is opened with Entreprise Manager, the application is locked by the Entreprise Manager. If i close the table the problem is solved and the application is unlocked and it can query the table. THE TABLE IS OPEN FOR VIEWING, not for design or update.
Can anyone help me? Thanks.

[valentindvf]

I use SQL Server 2000.
Thanks

[Patzer]

If you get the data of the table via recordsets, check the .locktype property of the recordset. Your description of the problem indicates that you use adlockpessimistic. That should be changed to .adlockoptimistic.

HTH

[valentindvf]

Hi,

Yes, I use recordset, but LockType = adLockOptimistic

Not this is the problem.

Thanks anyway.

[Doomsday]

Are you using a static, dynamic or unspecified cursor?

I have many multi-user apps using VB and SQL Server and I found that using batch updates and a static cursor gives the least problems i.e.

rs1.Open cmd1, , adOpenStatic, adLockBatchOptimistic

[valentindvf]

I use adOpenStatic and adLockOptimistic.

[hensa22]

I am not sure,but maybe it happens because user privilege into DB is Lock table on mode exclusive or something so.

oversee user privilege.

[valentindvf]

How can I change that?