March 8, 2006 at 2:04 am
Hi,
I have an odbc-connection from within MS Access to SQL Server. When I display the table in Access and I try to delete a record in SQL-Server, I have a locking, although the user on the ms-access side has only read-permissions.
How can I solve this problem?
March 9, 2006 at 5:19 am
March 9, 2006 at 8:17 am
To extend Carolyn's suggestion a bit: If you are using permissions in SQL Server fully (and you know that you should!), remove delete permissions from all tables and provide stored procedures for the FEW cases where it makes sense to allow a user to delete a record from a table.
An exception would be work tables, where it is intended that info is stored briefly, for a work session, then processed on to a permanent location and removed from the work table.
In my experience there are very few instances where it makes good sense to give users direct delete permissions on tables. Instead, you can remove that permission, create a stored procedure for deleting, and give permission to run that procedure.
In my current system, with 161 base tables, no one has permissions to delete from any table. I have two stored procedures. Each deletes at most a single row from a single table. I say "at most" because each has this form:
DELETE From MyTable
Where MyKey = @MyKEy
And MyFlagField = 'delete me'
If the text field is not loaded properly, no deletion occurs.
On the application side, my two delete buttons first update the selected record to place 'delete me' in my flag field. (Any text field will do. After all, you're about to delete the record.) The button code then calls the stored procedure, passing only the primary key of the record to delete.
An added plus, if you have any concern about need to archive deleted records, the stored procedure is your tool to archive before deleting.
And, BTW, about NOLOCK: If you look at Locking Hints in MSDN (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_1hf7.asp), you will see that NOLOCK applies only to SELECT statements. The article is for SS2K, but I believe it's the same in 2K5.
Rick
March 10, 2006 at 12:48 am
Users working from within Access have only read-permissions; the delete-statement is performed on the sql-server in a DTS-Package.
I've done some extra test and came out with the following:
1. Display the table (with read-only-permissions) in Access
2. Start the delete statement in the DTS-Package -> the statement blocks
3. Jump to the last record in Access -> The lock is released and the delete-statement can be performed and the records are still displayed in Access.
The problem is that almost all users in our company can use access to retrieve data and that there will always be one who does a select without jumping to the last record.
Thanks for your help
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply