A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

read/write for some users, read only form others



 
 
Thread Tools Display Modes
  #1  
Old May 31st, 2010, 04:26 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default 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  
Old May 31st, 2010, 04:48 PM posted to microsoft.public.access.forms
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default 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  
Old May 31st, 2010, 05:21 PM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default 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  
Old May 31st, 2010, 08:22 PM posted to microsoft.public.access.forms
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default 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  
Old June 1st, 2010, 09:53 AM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default 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  
Old June 1st, 2010, 10:54 AM posted to microsoft.public.access.forms
Stefan Hoffmann
external usenet poster
 
Posts: 991
Default 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  
Old June 1st, 2010, 11:19 AM posted to microsoft.public.access.forms
Bob H[_4_]
external usenet poster
 
Posts: 161
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.