how to unlock a recordset

[francky]

When 2 persons use the same form, the second can't confirm his operation

Here is the code :

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim StrSql As String
Set cn = CurrentProject.Connection
cn.BeginTrans
StrSql = "Select * FROM QryEtik WHERE etservice =" & Chr(34) & [Forms]![frminventaire]![inservice] & Chr(34) & " and etnum =" & [Forms]![frminventaire]![inetik]
rst.Open StrSql, cn, adOpenKeyset, adLockOptimistic
If rst.RecordCount = 0 Then
MsgBox "Le N° d'étiquette n'est pas attribué à la zone mentionnée"
Me.Undo
DoCmd.GoToControl "inetik"
Exit Sub
End If

Thanks for your help

Francky

 

[VBslammer]

WITH OWNERACCESS OPTION VBSlammer

 

[francky]

Can you give me more explanation

 

[VBslammer]

This is a well documented feature:

WITH OWNERACCESS OPTION Declaration
Description

In a multiuser environment with a secure workgroup, use this declaration with a query to give the user who runs the query the same permissions as the query's owner.

Syntax

sqlstatement
WITH OWNERACCESS OPTION

Remarks

The WITH OWNERACCESS OPTION declaration is optional.

The following example enables the user to view salary information (even if the user doesn't otherwise have permission to view the Payroll table), provided that the query's owner does have that permission:

SELECT LastName,
FirstName, Salary
FROM Employees
ORDER BY LastName
WITH OWNERACCESS OPTION;
If a user is otherwise prevented from creating or adding to a table, you can use WITH OWNERACCESS OPTION to enable the user to run a make-table or append query.

If you want to enforce workgroup security settings and users' permissions, don't include the WITH OWNERACCESS OPTION declaration.

This option requires you to have access to the System.mdw file associated with the database. It's really useful only in secured multiuser implementations.
See Also Permissions property ("DAO Language Reference&quot, SELECT statement.

Specifics (Microsoft Access)

You can include the WITH OWNERACCESS OPTION declaration in an SQL statement to allow users to run a query and view the results even if they don't have security permissions on the underlying tables. When this option is set, a user is granted the same permissions as the owner of the query.

For example, the owner of a query on an Employees table can include the WITH OWNERACCESS OPTION declaration in the definition of the query that returns all fields except those that give employees' addresses. A user who does not have read permissions on the Employees table can then run the query and view only those fields included in the query. In effect, the user now has restricted read access to the table.

The use of this declaration is equivalent to setting the RunPermissions property to Owner's in the query's property sheet in query Design view. Omitting the WITH OWNERACCESS OPTION declaration is equivalent to setting the RunPermissions property to User's, which is the default setting. VBSlammer

 

[francky]

Thanks for your help
I will try that asap

 

[cmmrfrds]

Unless you have some very special requirements, you should not be wrapping a select statement within a transaction. When you are ready to update then start the transaction if you need a transaction, which is when there is a logical transaction that includes multiple physical records to be syncronized in their update. For example, one account debited and another account credited with the same amount would be a logical transaction.

Show why (control flow) you need a transaction and then the problem can be addressed more specifically. DO NOT have user intervention within a transaction as you can see it is very bad design in a multi-user system.