delet query password

W

WilliamP61

Hi

I want to restrict the use of a delete query, which is run through a delete
record button on a form, by adding the need to enter a password. I would be
grateful for any suggestions of how I might achieve this.

Thank you

William
 
J

Jerry Whittle

To do it right, you need to set up user level security and maybe mess with
the query's Run Permissions property. That's quite a bit of work.

If you want to put a password on it without all the trouble of user level
security, modify the code below:

'-----
Private Sub Run_Query_Click()
On Error GoTo Err_Run_Query_Click

Dim Message, Title, Default, MyValue, stDocName

Message = "Enter Password" ' Set prompt.
Title = "Password" ' Set title.
stDocName = "qryJay" ' Set query

MyValue = InputBox(Message, Title)

If MyValue = "Password" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
Else
MsgBox "Wrong Password. Try again."
End If

Exit_Run_Query_Click:
Exit Sub

Err_Run_Query_Click:
MsgBox Err.Description
Resume Exit_Run_Query_Click

End Sub
'-----

Know that there are many, many ways to bypass this password such as going to
the database window and running the query there; going into the tables and
deleting the records directly; or opening up this form in design view and
finding the password.
 
P

Per Larsen

At least in Access 97 (which I still use mostly), you can replace

DoCmd.OpenQuery stDocName, acNormal, acEdit

with:
Dim qd As QueryDef

Set qd = CurrentDb().CreateQueryDef("", "DELETE FROM <Table> WHERE
<Criteria>")

qd.Execute


You then only run a temporary query, and have no permanent one in the
Queries window. However, you can not combine this with the "WITH
OWNERACCESS OPTION;", I think.

Hth
PerL
 
P

Per Larsen

Oh, and I forgot to add that you can even do it "on the fly" (even from
the Immediate window, for testing — minding line breaks below) using:

CurrentDb().CreateQueryDef("", "DELETE FROM <Table> WHERE
<Criteria>").Execute


PerL
 
P

Per Larsen

To mess us this thread even more, I recently stumbled over another
variant of the Execute method (in DAO):

CurrentDb.Execute "DELETE FROM <Table> WHERE <Criteria>")[, Options]

Where 'Options' is described in the Help page of the Execute method (in
Access).

I guess this correspond closely to the Execute method of the ADO
Connection object.

Hth
PerL
 
W

WilliamP61

Thanks Per

I need to investigare your suggestions further. I ended up using Jerry's
suggestion since it was simple and suited my apllication.

Thanks
William

Per Larsen said:
To mess us this thread even more, I recently stumbled over another
variant of the Execute method (in DAO):

CurrentDb.Execute "DELETE FROM <Table> WHERE <Criteria>")[, Options]

Where 'Options' is described in the Help page of the Execute method (in
Access).

I guess this correspond closely to the Execute method of the ADO
Connection object.

Hth
PerL



Oh, and I forgot to add that you can even do it "on the fly" (even from
the Immediate window, for testing — minding line breaks below) using:

CurrentDb().CreateQueryDef("", "DELETE FROM <Table> WHERE
<Criteria>").Execute


PerL
Click to expand...
Click to expand...
 
Top