By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,966 Members | 2,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,966 IT Pros & Developers. It's quick & easy.

Setting permissions (run time)

P: n/a
Hi All,
This maybe a really simple question but I need some help. I have been
having problems with security and thanks to the help received from a reply
to an earlier post, I have found a solution. However, this has thrown up a
few other complications. I have the following code where I need the Dlookup
function to run with owner permissions. To be more precise, I have taken
away read permissions on all the tables and all stored query's now run with
owner permissions which is fine. What I need is for the following code
(there is a lot more but I think I can figure it out once given an example)
to run even though the user has no permission to read the table!!!!

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"If you enter an anvalid user ID 3 times, the database will
close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
Me.userinput.Value = Null
Exit Sub
End If

Many thanks in advance,

Mark
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Mark,
Wouldn't the simplest thing be to not use DLookUp?

You're on the right track using locked tables and OwnerPermissions queries
so stick with that, you know it works. Just get rid of the "spreadsheet
spawned" domain functions.
--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:1L***************@newsfe2-win.ntli.net...
Hi All,
This maybe a really simple question but I need some help. I have been
having problems with security and thanks to the help received from a reply
to an earlier post, I have found a solution. However, this has thrown up a
few other complications. I have the following code where I need the Dlookup function to run with owner permissions. To be more precise, I have taken
away read permissions on all the tables and all stored query's now run with owner permissions which is fine. What I need is for the following code
(there is a lot more but I think I can figure it out once given an example) to run even though the user has no permission to read the table!!!!

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"If you enter an anvalid user ID 3 times, the database will
close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
Me.userinput.Value = Null
Exit Sub
End If

Many thanks in advance,

Mark

Nov 13 '05 #2

P: n/a
Hi Terry,
Yes, ,it would be easier to not use Dlookup but being new to
dabbling with VB, I don't know how else to do it!. I have tried
declaring an SQL in code but that has not worked!!! Any suggestions
would be most appreciated :o)

Regards,

Mark

"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<66********************@karoo.co.uk>...
Mark,
Wouldn't the simplest thing be to not use DLookUp?

You're on the right track using locked tables and OwnerPermissions queries
so stick with that, you know it works. Just get rid of the "spreadsheet
spawned" domain functions.
--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:1L***************@newsfe2-win.ntli.net...
Hi All,
This maybe a really simple question but I need some help. I have been
having problems with security and thanks to the help received from a reply
to an earlier post, I have found a solution. However, this has thrown up a
few other complications. I have the following code where I need the

Dlookup
function to run with owner permissions. To be more precise, I have taken
away read permissions on all the tables and all stored query's now run

with
owner permissions which is fine. What I need is for the following code
(there is a lot more but I think I can figure it out once given an

example)
to run even though the user has no permission to read the table!!!!

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"If you enter an anvalid user ID 3 times, the database will
close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
Me.userinput.Value = Null
Exit Sub
End If

Many thanks in advance,

Mark

Nov 13 '05 #3

P: n/a
Look at creating a query (qUserID) with SQL something like

PARAMETERS TestUserID Text ( 255 );
SELECT accees_ids.UserID
FROM accees_ids
WHERE (((accees_ids.UserID)=[TestUserID]))
WITH OWNERACCESS OPTION;
Then depending on whether you are using ADO or DAO include one of the
following functions in your db

Function ValidUserIDDAO(strUserID As String) As Boolean
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim loRst As DAO.Recordset

Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qUserID")
With loQdf
.Parameters(0) = strUserID
Set loRst = .OpenRecordset
End With

With loRst
ValidUserIDDAO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function

Function ValidUserIDADO(strUserID As String) As Boolean
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset

Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "qUserID"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array(strUserID))
End With

With loRst
ValidUserIDADO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function
Your code then becomes
If not ValidUserDAO(Me.userinput.Value) Then ...
or
If not ValidUserADO(Me.userinput.Value) Then ...

Depending on which funciton your using.

--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:11*************************@posting.google.co m...
Hi Terry,
Yes, ,it would be easier to not use Dlookup but being new to
dabbling with VB, I don't know how else to do it!. I have tried
declaring an SQL in code but that has not worked!!! Any suggestions
would be most appreciated :o)

Regards,

Mark

"Terry Kreft" <te*********@mps.co.uk> wrote in message

news:<66********************@karoo.co.uk>...
Mark,
Wouldn't the simplest thing be to not use DLookUp?

You're on the right track using locked tables and OwnerPermissions queries so stick with that, you know it works. Just get rid of the "spreadsheet
spawned" domain functions.
--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:1L***************@newsfe2-win.ntli.net...
Hi All,
This maybe a really simple question but I need some help. I have been having problems with security and thanks to the help received from a reply to an earlier post, I have found a solution. However, this has thrown up a few other complications. I have the following code where I need the

Dlookup
function to run with owner permissions. To be more precise, I have taken away read permissions on all the tables and all stored query's now run

with
owner permissions which is fine. What I need is for the following code
(there is a lot more but I think I can figure it out once given an

example)
to run even though the user has no permission to read the table!!!!

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _ "If you enter an anvalid user ID 3 times, the database will close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
Me.userinput.Value = Null
Exit Sub
End If

Many thanks in advance,

Mark

Nov 13 '05 #4

P: n/a
Terry,
I don't understand fully what the code you have supplied me with is
doing but it works. Thanks very much.

Much appreciated.

Mark

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:yB********************@karoo.co.uk...
Look at creating a query (qUserID) with SQL something like

PARAMETERS TestUserID Text ( 255 );
SELECT accees_ids.UserID
FROM accees_ids
WHERE (((accees_ids.UserID)=[TestUserID]))
WITH OWNERACCESS OPTION;
Then depending on whether you are using ADO or DAO include one of the
following functions in your db

Function ValidUserIDDAO(strUserID As String) As Boolean
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim loRst As DAO.Recordset

Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qUserID")
With loQdf
.Parameters(0) = strUserID
Set loRst = .OpenRecordset
End With

With loRst
ValidUserIDDAO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function

Function ValidUserIDADO(strUserID As String) As Boolean
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset

Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "qUserID"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array(strUserID))
End With

With loRst
ValidUserIDADO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function
Your code then becomes
If not ValidUserDAO(Me.userinput.Value) Then ...
or
If not ValidUserADO(Me.userinput.Value) Then ...

Depending on which funciton your using.

--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:11*************************@posting.google.co m...
Hi Terry,
Yes, ,it would be easier to not use Dlookup but being new to
dabbling with VB, I don't know how else to do it!. I have tried
declaring an SQL in code but that has not worked!!! Any suggestions
would be most appreciated :o)

Regards,

Mark

"Terry Kreft" <te*********@mps.co.uk> wrote in message

news:<66********************@karoo.co.uk>...
> Mark,
> Wouldn't the simplest thing be to not use DLookUp?
>
> You're on the right track using locked tables and OwnerPermissions queries > so stick with that, you know it works. Just get rid of the
> "spreadsheet
> spawned" domain functions.
>
>
> --
> Terry Kreft
> MVP Microsoft Access
>
>
> "Mark" <ma*********@ntlworld.com> wrote in message
> news:1L***************@newsfe2-win.ntli.net...
> > Hi All,
> > This maybe a really simple question but I need some help. I have been > > having problems with security and thanks to the help received from a reply > > to an earlier post, I have found a solution. However, this has thrown up a > > few other complications. I have the following code where I need the
> Dlookup
> > function to run with owner permissions. To be more precise, I have taken > > away read permissions on all the tables and all stored query's now
> > run
> with
> > owner permissions which is fine. What I need is for the following
> > code
> > (there is a lot more but I think I can figure it out once given an
> example)
> > to run even though the user has no permission to read the table!!!!
> >
> > ' count user id entry attempts
> > If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
> > Me.userinput.Value & "'")) Then
> > Me.uidchk.Value = Me.uidchk.Value + 1
> > If Me.uidchk.Value > 3 Then
> > MsgBox "You may not make more than three User ID entry
> > attempts.", vbCritical + vbOKOnly, "Oops!"
> > DoCmd.Quit
> > End If
> > MsgBox Me.userinput.Value & " is not a valid account" &
> > vbCrLf & _ > > "If you enter an anvalid user ID 3 times, the database will > > close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
> > Me.userinput.Value = Null
> > Exit Sub
> > End If
> >
> > Many thanks in advance,
> >
> > Mark
> >
> >


Nov 13 '05 #5

P: n/a
Hi Terry,
I should have posted the full code before I sent my first post.... I
have managed to get the first 2 parts working but the last 2 I am struggling
with ('Check to see if the password needs changing & 'check to see if the
password has expired).

Could you offer any more advice?
Private Sub userinput_AfterUpdate()
' Validate user id (name)
If IsNull(Me.userinput.Value) Or Me.userinput.Value = "" Then
MsgBox "Enter your User ID (Name) in the User ID field.", vbOKOnly,
"Missing User ID"
Me.passwordinput.Value = Null
Me.passwordinput.Visible = False
Me.userinput.SetFocus
Exit Sub
End If

' count user id entry attempts
If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
Me.userinput.Value & "'")) Then
Me.uidchk.Value = Me.uidchk.Value + 1
If Me.uidchk.Value > 3 Then
MsgBox "You may not make more than three User ID entry
attempts.", vbCritical + vbOKOnly, "Oops!"
DoCmd.Quit
End If
MsgBox Me.userinput.Value & " is not a valid account" & vbCrLf & _
"If you enter an anvalid user ID 3 times, the database will
close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
Me.userinput.Value = Null
Exit Sub
End If

'Check to see if the password needs changing
If DLookup("[pwd]", "accees_ids", "[userid] = forms!access_login!userinput")
= Forms!access_login!userinput Then
MsgBox "As this is the first time you have used the database," _
& vbCrLf & "You are required to set a password", vbCritical = vbOKOnly,
"Change Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
'check to see if the password has expired
If DateDiff("m", DLookup("[chng_pwd]", "accees_ids", "[userid] =
forms!access_login!userinput"), Now()) >= 3 Then
MsgBox "3 months have passed!! You need to change your password",
vbCritical = vbOKOnly, "Expired Password"
Me.txtNewPW.Visible = True
Me.passwordinput.Visible = False
Me.openmain.Visible = False
Exit Sub
End If
' user id exists, continue
Me.passwordinput.Visible = True
Me.passwordinput.SetFocus

End Sub
"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:yB********************@karoo.co.uk...
Look at creating a query (qUserID) with SQL something like

PARAMETERS TestUserID Text ( 255 );
SELECT accees_ids.UserID
FROM accees_ids
WHERE (((accees_ids.UserID)=[TestUserID]))
WITH OWNERACCESS OPTION;
Then depending on whether you are using ADO or DAO include one of the
following functions in your db

Function ValidUserIDDAO(strUserID As String) As Boolean
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef
Dim loRst As DAO.Recordset

Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qUserID")
With loQdf
.Parameters(0) = strUserID
Set loRst = .OpenRecordset
End With

With loRst
ValidUserIDDAO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function

Function ValidUserIDADO(strUserID As String) As Boolean
Dim loDB As ADODB.Connection
Dim loQdf As ADODB.Command
Dim loRst As ADODB.Recordset

Set loDB = Application.CurrentProject.Connection
Set loQdf = New ADODB.Command
With loQdf
Set .ActiveConnection = loDB
.CommandText = "qUserID"
.CommandType = adCmdStoredProc
Set loRst = .Execute(Parameters:=Array(strUserID))
End With

With loRst
ValidUserIDADO = Not (.EOF And .BOF)
.Close
End With
Set loRst = Nothing
Set loQdf = Nothing
Set loDB = Nothing
End Function
Your code then becomes
If not ValidUserDAO(Me.userinput.Value) Then ...
or
If not ValidUserADO(Me.userinput.Value) Then ...

Depending on which funciton your using.

--
Terry Kreft
MVP Microsoft Access
"Mark" <ma*********@ntlworld.com> wrote in message
news:11*************************@posting.google.co m...
Hi Terry,
Yes, ,it would be easier to not use Dlookup but being new to
dabbling with VB, I don't know how else to do it!. I have tried
declaring an SQL in code but that has not worked!!! Any suggestions
would be most appreciated :o)

Regards,

Mark

"Terry Kreft" <te*********@mps.co.uk> wrote in message

news:<66********************@karoo.co.uk>...
> Mark,
> Wouldn't the simplest thing be to not use DLookUp?
>
> You're on the right track using locked tables and OwnerPermissions queries > so stick with that, you know it works. Just get rid of the
> "spreadsheet
> spawned" domain functions.
>
>
> --
> Terry Kreft
> MVP Microsoft Access
>
>
> "Mark" <ma*********@ntlworld.com> wrote in message
> news:1L***************@newsfe2-win.ntli.net...
> > Hi All,
> > This maybe a really simple question but I need some help. I have been > > having problems with security and thanks to the help received from a reply > > to an earlier post, I have found a solution. However, this has thrown up a > > few other complications. I have the following code where I need the
> Dlookup
> > function to run with owner permissions. To be more precise, I have taken > > away read permissions on all the tables and all stored query's now
> > run
> with
> > owner permissions which is fine. What I need is for the following
> > code
> > (there is a lot more but I think I can figure it out once given an
> example)
> > to run even though the user has no permission to read the table!!!!
> >
> > ' count user id entry attempts
> > If IsNull(DLookup("[userid]", "accees_ids", "[UserID]='" &
> > Me.userinput.Value & "'")) Then
> > Me.uidchk.Value = Me.uidchk.Value + 1
> > If Me.uidchk.Value > 3 Then
> > MsgBox "You may not make more than three User ID entry
> > attempts.", vbCritical + vbOKOnly, "Oops!"
> > DoCmd.Quit
> > End If
> > MsgBox Me.userinput.Value & " is not a valid account" &
> > vbCrLf & _ > > "If you enter an anvalid user ID 3 times, the database will > > close!!! ", vbOKOnly, "User ID " & Me.userinput.Value & " Invalid"
> > Me.userinput.Value = Null
> > Exit Sub
> > End If
> >
> > Many thanks in advance,
> >
> > Mark
> >
> >


Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.