If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
read/write for some users, read only form others
I have a database at work which I built in Access 2007, but then had to
save it as a Access 2003 mdb format. The database was then placed on a NAS server and split. The front end was then placed on local users PC's, some of which have Access 2003. I now want to restrict write access to some users and only allow 2 users to do that, while all can have read access. How can I achieve this situation and what is the best or easiest way of doing so. Thanks |
#2
|
|||
|
|||
read/write for some users, read only form others
hi Bob,
On 31.05.2010 17:26, Bob H wrote: I now want to restrict write access to some users and only allow 2 users to do that, while all can have read access. How can I achieve this situation and what is the best or easiest way of doing so. There is not really an easy way. There are two steps: 1) Implement a security model. 2) Change your forms in such a manner that these restrictions are properly used - the forms AllowDelete, AllowEdit and AllowInsert properties must be set accordingly to the permissions. In your case I would recommend using SQL Server 2008 Express as database backend (it's free). Because you can use integrated Windows security and manage the permissions in the AD. Under normal circumstances your application (front-end) should work without flaws, but there are some pitfalls. See JStreets whitepaper 'The Best of Both Worlds: Access-SQL Server Optimization' at http://www.jstreettech.com/cartgenie...rDownloads.asp The other option is to use the Access integrated security model: http://office.microsoft.com/en-us/ac...662271033.aspx There is also a "third" option: Implement a kind of user login and role membership yourself. mfG -- stefan -- |
#3
|
|||
|
|||
read/write for some users, read only form others
On 31/05/2010 16:48, Stefan Hoffmann wrote:
hi Bob, On 31.05.2010 17:26, Bob H wrote: I now want to restrict write access to some users and only allow 2 users to do that, while all can have read access. How can I achieve this situation and what is the best or easiest way of doing so. There is not really an easy way. There are two steps: 1) Implement a security model. 2) Change your forms in such a manner that these restrictions are properly used - the forms AllowDelete, AllowEdit and AllowInsert properties must be set accordingly to the permissions. In your case I would recommend using SQL Server 2008 Express as database backend (it's free). Because you can use integrated Windows security and manage the permissions in the AD. Under normal circumstances your application (front-end) should work without flaws, but there are some pitfalls. See JStreets whitepaper 'The Best of Both Worlds: Access-SQL Server Optimization' at http://www.jstreettech.com/cartgenie...rDownloads.asp The other option is to use the Access integrated security model: http://office.microsoft.com/en-us/ac...662271033.aspx There is also a "third" option: Implement a kind of user login and role membership yourself. mfG -- stefan -- Hi Stefan, thanks for the information and links. From what I have been reading, I agree, there does not seem to be an easy way at all. In fact I would go so far as to say even complicated in some of what I have read, for a relative newbie like me. Security and passwords in Access is a whole new ball game, like something else to learn before implementing any of it. All I want to do is stop some users from editing the data on the forms, so maybe I should go for the third option. So how can I do that. I have been playing around with security and passwords on my own PC, but that is different from the situation at work. For example, if I set a password for myself that only requires me to login, so how can I set a password for another user with write access, and then one for users with read only access. Thanks |
#4
|
|||
|
|||
read/write for some users, read only form others
hi Bob,
On 31.05.2010 18:21, Bob H wrote: All I want to do is stop some users from editing the data on the forms, so maybe I should go for the third option. So how can I do that. I have been playing around with security and passwords on my own PC, but that is different from the situation at work. For example, if I set a password for myself that only requires me to login, so how can I set a password for another user with write access, and then one for users with read only access. In this case you need at least an user table, e.g. Id, UserName and an permission table, e.g. FormPermission idUser, FormName, AllowDelete, AllowEdit, AllowInsert. Open at the application start a form to query the user, e.g. use the Windows account name http://www.mvps.org/access/api/api0008.htm After closing the this login form, store the user id in a local table in the front-end, e.g. CurrentUser. Use a public funtion in a standard module to set the permissions: Public Function FormApplyPermissions(AForm As Access.Form) As Boolean On Local Error GoTo LocalError Dim rs As DAO.Recordset Dim sql As String sql = "SELECT FP.* FROM CurrentUser CU " & _ "INNER JOIN FormPermission FP " "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot) If Not rs.Bof And Not rs.Eof Then AForm.AllowDelete = rs.AllowDelete AForm.AllowEdit = rs.AllowEdit AForm.AllowInsert = rs.AllowInsert Else MsgBox "Unkown form." AForm.AllowDelete = False AForm.AllowEdit = False AForm.AllowInsert = False End If rs.Close Set rs = Nothing Exit Function LocalError: MsgBox "D'oh!" & vbCrLf & Err.Description End Function Use this function in all form load events: Private Sub Form_Load() FormApplyPermissions Me.Form End Sub mfG -- stefan -- |
#5
|
|||
|
|||
read/write for some users, read only form others
On 31/05/2010 20:22, Stefan Hoffmann wrote:
hi Bob, On 31.05.2010 18:21, Bob H wrote: All I want to do is stop some users from editing the data on the forms, so maybe I should go for the third option. So how can I do that. I have been playing around with security and passwords on my own PC, but that is different from the situation at work. For example, if I set a password for myself that only requires me to login, so how can I set a password for another user with write access, and then one for users with read only access. In this case you need at least an user table, e.g. Id, UserName and an permission table, e.g. FormPermission idUser, FormName, AllowDelete, AllowEdit, AllowInsert. Open at the application start a form to query the user, e.g. use the Windows account name http://www.mvps.org/access/api/api0008.htm After closing the this login form, store the user id in a local table in the front-end, e.g. CurrentUser. Use a public funtion in a standard module to set the permissions: Public Function FormApplyPermissions(AForm As Access.Form) As Boolean On Local Error GoTo LocalError Dim rs As DAO.Recordset Dim sql As String sql = "SELECT FP.* FROM CurrentUser CU " & _ "INNER JOIN FormPermission FP " "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot) If Not rs.Bof And Not rs.Eof Then AForm.AllowDelete = rs.AllowDelete AForm.AllowEdit = rs.AllowEdit AForm.AllowInsert = rs.AllowInsert Else MsgBox "Unkown form." AForm.AllowDelete = False AForm.AllowEdit = False AForm.AllowInsert = False End If rs.Close Set rs = Nothing Exit Function LocalError: MsgBox "D'oh!" & vbCrLf & Err.Description End Function Use this function in all form load events: Private Sub Form_Load() FormApplyPermissions Me.Form End Sub mfG -- stefan -- Hi Stefan, I have created tblUser and tblPermissions with the required fields as well as creating a module for network login as per the link, but should this be a form? Then I copied and pasted the code above into a new module, but when I come to save it, there is a compile error he "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" Thanks |
#6
|
|||
|
|||
read/write for some users, read only form others
hi Bob,
On 01.06.2010 10:53, Bob H wrote: Then I copied and pasted the code above into a new module, but when I come to save it, there is a compile error he "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" There is a line continuation sign missing after the ampersand. Change it to ..CU.idUser " & _ ".. mfG -- stefan -- |
#7
|
|||
|
|||
read/write for some users, read only form others
On 01/06/2010 10:54, Stefan Hoffmann wrote:
hi Bob, On 01.06.2010 10:53, Bob H wrote: Then I copied and pasted the code above into a new module, but when I come to save it, there is a compile error he "ON FP.idUser = CU.idUser " & "WHERE FP.FormName ='" & AForm.Name & "';" There is a line continuation sign missing after the ampersand. Change it to ..CU.idUser " & _ ".. mfG -- stefan -- Still getting compile error: Compile Error Expected: line number or label or statement or end of statement. Thanks |
Thread Tools | |
Display Modes | |
|
|