Delete Permissions (Access 2002, SQL Server 2000)

[tjuni]

I have an access front-end linked to a sql server back-end. Is there any way that I can set permissions to allow users to delete data in individual fields, but not allow them to delete an entire record? Also - another entirely unrelated question - some of my users can open multiple copies of the front-end on their computers while others can only open one. How can I change this? what causes it? I know that it may be dangerous for users to be able to open multiple copies because of record-locking - but I may want to find a way to allow it, anyway. Or, I may want to disallow it. In any case, I'd like to know how to control this feature.

Thanks!

[HansV]

There are probably ways to grant/revoke permissions in SQL Server, but others will have to help you with that. In Access itself, you can set the Allow Edits property of a form to Yes and the Allow Deletions property to No. Users will be able to modify the contents of fields (including clearing the contents), but they won't be able to delete records.

Others will also have to answer the question about multiple instances of a database on the same PC.

[tjuni]

Thanks, Hans. I'll try the allow deletions property right now!

[WendellB]

Hans has you on the right track with Access. With respect to SQL Server, there are somewhat similar permissions that can be assigned to each SQL user or to a group of SQL users. The simplest way to manage SQL security is to use the integrated version so you work with an NT-domain login. The actual administration of security is easiest to manage through the Enterprise Manager, although you can use stored procedures. The latter is pretty tedious. Also, be aware that with ODBC attached tables, the Access permissions on tables (and views which are treated as tables by Access) don't really do anything, so you may need to set security in SQL Server if you are letting people get at tables.

[Support4John]

Hi Tabie

Don't remember where I got it, I use the following called from hidden startup form:

HTH

John

===============================
Option Compare Database
Option Explicit

' ---Posted by Graham Mandeno---

' --------------------------------------------------------------------------------
' API: Preventing multiple instances of a database
' --------------------------------------------------------------------------------


' The simplest way to ensure that only one instance of the database can be opened on one
' desktop is to open the mdb file exclusively.

' However, with the shared mode set, if you have the Application Title set under
' Tools/Startup, another way would be to iterate through all windows at startup and
' display a warning message if a window's caption matches the Application Title.

' This solution uses the titlebar of the database window. It checks each other instance
' of Access currently running and if the titlebar of the ODb class window matches the
' active instance then it activates the other instance and terminates the current one.
' An optional boolean argument fConfirm causes a confirmation message to be displayed
' before switching and terminating (the default for fConfirm is True). The function
' winCheckMultipleInstances can be called from initialization code, or even directly
' from AutoExec:

' RunCode
' =winCheckMultipleInstances(False)

'******************** Code Start ********************

' Module mdlCheckMultipleInstances
'

[SteveH]

For info: The code came from The Access Web

[tjuni]

Thanks for the code, John. Actually, I was hoping there was some sort of Windows setting to change, or something to that effect. Since some people can open multiple instances of the db and others cannot, I was thinking that it must have to do with something beyond the db itself. You see, I'm not absolutely sure that I want to prevent opening multiple instances of the db. Maybe I prefer to allow it. But, I don't know how to do that [img]/forums/images/smilies/sad.gif[/img] .

Wendell - thanks for your response. I just found out, while reading the Access Help on the record-locking property, that Access record-locking does not work on a Sql Server back-end. I guess the only way I can deal with record-locking in my situation (access front, sql server back) is to take care of it on sql server, huh? Can you point me in the direction of some good reading material? Thanks.

[WendellB]

As far as record locking, that doesn't have anything to do with preventing records from being deleted. It deals with the editing of a record by two or more people, and SQL Server has a scheme similar to that of Access. With ODBC-linked tables you will occasionally get an error that indicates someone else has edited the record while you were trying to edit it.

The ability to delete a record in a given table is determined by the security settings in SQL Server. There are at least a couple of books available that are focused on SQL Server for the Access developer - "Microsoft Access Developer's Guide to SQL Server" and "SQL: Access to SQL Server". There is also lots of good advise in The Access Developer's Handbook and in the second volume of Allison Balter's book on 2002. Otherwise you are left to read lots of dry SQL Server books, or resort to the SQL Server Help files.

[tjuni]

Wendell - thanks for the recommendations. I'm not worried about delete permissions anymore b/c the Allow Deletions property on the form is doing the trick for me. What I am worried about is the fact that 2 users can open the same record at the same time. THe locking seems to be optimistic i.e. whoever makes the changes first gets his changes made. This is no good for me. I don't want 2 users to be able to access a record in a form at the same time. The first one who opens it should lock it to any other users while he has it open. i.e. I want pessimistic locking. However, even after reading through the locking articles on SQL Server HELP, I have no clue how to do this. The only thing that I thought of was to convert all form recordsets to ADO ones that I open with pessimistic locking - but that would be a real pain. Any ideas would be appreciated greatly!

[Support4John]

Taibe

In code I provided in previous post you can uncomment the code to provide multiple open db's

John

06/14/02 original code sMyCaption = Build : Database
'If MsgBox(sMyCaption & " is already open@" _
' & "Do you want to open a second instance of this database?@", _
' vbYesNo Or vbQuestion Or vbDefaultButton2) = vbYes Then
' Exit Function