Understandable documentation regarding Access DBA security

[Peter]

Hi all, and thanks for this great place...! Were can i find a easy to read,
step by step documentation on how i set up the database security. I am now
more or less ready with my application and i intend to splitt it into a
front- and back end. 10 users. The back end resides on a shared drive. All i
need is for each user to log in with his/her username and PW. This is
important since i also have a module that "picks up" the username and
populates the fOSUserName controll...

Thanks!

 

[Douglas J. Steele]

Unfortunately, Access User-Level Security isn't a simple proposition.

The most comprehensive document is
http://support.microsoft.com/support/access/content/secfaq.asp Make sure you
read it thoroughly several times before starting, work on a copy of your
database (just in case!) and make sure you don't leave out any steps.

 

[Peter]

Hi again...this documentation refers to Access 2.0-2000..i use 2003..is it
valid for me?

Thanks!

 

[Douglas J. Steele]

Yes. Nothing changed with respect to ULS in Access 2002 or Access 2003 or
MDB files in Access 2007.

 

[Tony Toews [MVP]]

Hi all, and thanks for this great place...! Were can i find a easy to read,
step by step documentation on how i set up the database security. I am now
more or less ready with my application and i intend to splitt it into a
front- and back end. 10 users. The back end resides on a shared drive. All i
need is for each user to log in with his/her username and PW. This is
important since i also have a module that "picks up" the username and
populates the fOSUserName controll...

Why not just use the network user id? API: Get Login name
http://www.mvps.org/access/api/api0008.htm

Do you really need to secure the objects within the MDB file, either
FE or BE?

Tony

 

[Peter]

Hi Tony.....here is what i need..

"/Hi again...i have an issue with "picking up" the username of the Windows
system.

http://www.mvps.org/access/api/api0008.htm. I copied the whole code and i
pasted it in a module (Module1) this is how it looks:

Option Compare Database

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
______________________________________________________________
Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function

I followed the conversation Subject: Re: Returning current user from SQLServer

And the bottom line is, this is what i need. I want the username to be
posted on my opening form (whiwch is always open when the application runs)
in order to refer to it later. I have created a textbox, "Username". On the
opening event of my form i use DoCmd.Maximize...were do i put Me.txtUserName
= fOSUserName????

fOSUserName is part of my client table...whoever creates or modifies a
record will be populated here...

I appologize for my ignorance

 

[Tony Toews [MVP]]

And the bottom line is, this is what i need. I want the username to be
posted on my opening form (whiwch is always open when the application runs)
in order to refer to it later.

That's what I was wondering. Rather than sending you way off into the
complex world of Access security.

And I see Douglas has replied.

I appologize for my ignorance

We don't mind ignorance because we like helping. So continue to ask
away.

Tony

 

[Peter]

Ok Tony...i am using this

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time and current user name.
Me.DateModified = Date
Me.TimeModified = Time()
Me.fOSUserName = fOSUserName()
BeforeUpdate_End:
Exit Sub
BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub

The module to pick up the API code is in place...but the bound controll is
still not populated i use the above code on a Before Update event for the
Form..and the time and date functions....i beleive i am doing something very
wrong..

 

[Douglas J. Steele]

Try using something other than fOSUserName as the name of the text box. The
ambiguity is probably confusing Access.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Peter]

Hoping you are a person of great patiance...-..

Nope..if i use the below code..it does not update my bounded Operator text
field with the current user when i edit this specific record...

If i set the control source to = fOSUserName() it populates this text
field...but regardless if the record is editet or not..just by opening it...

I need to know whois the last person that edited the record...the time and
date functions well..displays the current date/time the record was
edited...after that i exit the record...thats ok

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time and current user name.
Me.DateModified = Date
Me.TimeModified = Time()
Me.Operator = fOSUserName()
BeforeUpdate_End:
Exit Sub
BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdat

 

[Douglas J. Steele]

So you're saying that the DateModified and TimeModified fields are being
updated (why are you storing them in separate fields, btw? You should have a
single field and populate it using the Now function), but that Operator
isn't? That makes no sense.

For debugging purposes, try the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

MsgBox "Before: Operator " & Me.Operator & vbCrLf & _
"(" & Me.DateModified & " " & Me.TimeModified & ")" & vbCrLf & _
"fOSUserName = " & fOSUserName()

' Set bound controls to system date and time and current user name.
Me.DateModified = Date
Me.TimeModified = Time()
Me.Operator = fOSUserName()

MsgBox "After: Operator " & Me.Operator & vbCrLf & _
"(" & Me.DateModified & " " & Me.TimeModified & ")"

BeforeUpdate_End:
Exit Sub

BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End

End Sub

 

[Peter]

When i replace my code with yours i receive two small popups after exiting
the record..i did some edits

1. Before: Operator (29/05/2009 17:51:48) fOSUername =
2. After: Operator (29/05/2009 17:54:16)

 

[Peter]

...and the reson for the seperate Time Date fieled is this idea that i want to
caclulate the elapsed tome between time created and time modified in the
future..just an experiment...

 

[David W. Fenton]

When i replace my code with yours i receive two small popups after
exiting the record..i did some edits

1. Before: Operator (29/05/2009 17:51:48) fOSUername =
2. After: Operator (29/05/2009 17:54:16)

Your function is not working.

 

[Peter]

I have not changed the name of the Module other then the default,
Module1...the function part of the module was automatically named
fOSUsername...and if i use the controll source in a unbound textfield =
fOSUername ()..it does return the correct value...is there any other way to
bypass this problem...the update event functions well for date and time...so
i know when the record was last edited..but not by whome..

 

[Douglas J. Steele]

DateDiff works just as well with Date/Time as with Time only.

 

[Douglas J. Steele]

Like David says, there's a problem with your fOSUserName function. Are you
sure that it still works if you set the ControlSource of a text box to
=fOSUserName?

 

[Peter]

Yes Douglas. Unbound textbox with controll source =fOSUserName() works just
fine..perhaps i should just set an invisible textbox with =fOSUserName() and
refer to that the following way:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo BeforeUpdate_Err

' Set bound controls to system date and time and current user name.
Me.DateModified = Date
Me.TimeModified = Time()
Me.Username = Forms!Spider_reports.UserName

The date
BeforeUpdate_End:
Exit Sub
BeforeUpdate_Err:
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End
End Sub


I will deffently use the DateDiff in order to be able to calculate the time
spent on a specific record...Thanks..well..step by step...day by day..

 

[Peter]

Hmm..does access need to modify the advapi32.dll...i am not the administrator

 

[Douglas J. Steele]

Shouldn't need to be administrator, and I'm unaware of any interactions
between Access and advapi32.dll