request advice on protecting records

[jer007]

I'm just looking for some input on the best way to avoid users from deleting records in my database. I'm trying to avoid having to use logins so if you know of any good ways I'd appriciate the help

Thanks

-Jeremy

 

[luceze]

At the form level you can set the Allow Deletions property to false to prevent users from deleting records. However, if they have access to the tables then there is nothing to prevent them from deleting records straigt from the table. If you implement Access security then you can restrict the users from being able to do numerous things. There are several faqs at this site on this topic. You can also set the startup options to hide the database window so they could not see the tables or queries but that is no guarantee that they could not get around this (shift-open).

HTH,
Eric

 

[jer007]

Thanks,

Is there a way to have a delete record button on the form but before it would delete the record the user would have to enter a password?

 

[luceze]

Sure. This will delete the currently selected record after the user enters the correct password.

strPass = InputBox("Enter password to confirm deletion of current record.", _
"Password")
If strPass = "12345" Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Else
MsgBox "Invalid password", vbOKOnly, "Error"
End If

HTH,
Eric

 

[RoyVidar]

Or - here's another version...

Placing the "password" coding in the forms Before Del Confirm event, which occurs after the user deletes to the buffer, but before Access displays the default "you are about to..." msgbox.

Here you can both cancel the event, and avoid the default message.

If the users are able to delete thru using the recordselector and delete, then this method would trap for that.

The routine might look something like this:

[tt]private sub form_beforedelconfirm(cancel as integer, response as integer)
response=acdataerrcontinue ' turn off default msg
if inputbox("Password")<> "YourPassword" then
cancel = true
end if
end sub[/tt]

If you use your own wizard crated delete button, then add to the errorhandling routine also a trap for the cancelling of this event (when the user doesn't provide the correct password):

[tt]...
Err_cmbDelete_Click:
if err.number <> 2501 then
msgbox err.description
end if
resume Exit_cmbDelete_Click
end sub[/tt]

Roy-Vidar

 

[jer007]

RoyVidar

I tried your soloution however, when I use my delete command button the record just deletes and doesn't prompt for the password. Do you know what would be wrong? I copied your code as you displayed it.

thanks

 

[RoyVidar]

...and put it in the forms before del confirm event?

Use Debug | Compile, and save...

Put a breakpoint in the first executable line (response =...) by hitting F9. Then go back to the form and see if it executes. You should be thrown into the VBE with that line higlighted when trying to delete.

Else ???;-)

Roy-Vidar

 

[jer007]

This is odd, the records just delete as soon as I click the delete button. I don't get prompted for a password. When I put a breakpoint in the first executable line (response=) like you sugested nothing happens, it doesn't go to the code. I'm starting to wonder if it's not even executing that code at all.

here's my code for the BeforeDelConfirm

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue ' turn off default msg
If InputBox("password") <> "password" Then
Cancel = True
End If
End Sub

This is the code for the onclick delete command button

Private Sub cmbDelete_Click()
On Error GoTo Err_cmbDelete_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmbDelete_Click:
Exit Sub

Err_cmbDelete_Click:
If Err.Number <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_cmbDelete_Click
End Sub

Please let me know if you see what may be missing.

Thanks

 

[RoyVidar]

Yes, it seems the code is not executed at all.

Only way I'm able to recreate this error on my setup, is by turning warnings off (Tools | Options - Edit/Find tab, where Confirm Record changes is unchecked). This is something I never dare do, in case objects get deleted without warnings. It seems turning this off, also turns off the "delete warning events".

I'm not sure, but some of this behaviour might perhaps also come from having used:

[tt]docmd.setwarnings false[/tt]

without setting to true afterwards.

I consider both of these options very dangerous.

Roy-Vidar

 

[jer007]

RoyVidar

This is where I get very confused. The Confirm Record changes under Tools|Options is checked. I have nver used:
docmd.setwarnings false

however, I added docmd.setwarnings true to be sure that the warnings are on however, I don't get any warnings when I delete records, they just delete.

When I use the sugestion that luceze made then I get prompted for a password and if it's incorrect it doesn't delete records however, when I enter the password it displays the actual text instead of "*****" in place of the characters.

Is there a way to change the input so it only displays the astrix so other users can't see what the p/w is?

 

[luceze]

The inputbox method does not have the capabilities (as of Access 2002) of using the password input mask that I know of. You would have to create a pop up form to do this.

Eric

 

[RoyVidar]

Yes, the inputbox does not have the ability to "mask password", you would need to create a new form for that (or use a control on the form you make visible on the form for this purpose) with input mask "Password".

Just had a lookup in my reference to be sure (Developers Handbook 2000, Getz et al, page 39) saying this:

"If you have set the Confirm Record Changes option in the Edit/Find tab of the Tools -> Options dialog to false (or have used the SetOption method of the Application object to set the equivalent using VBA code), the BeforeDelConfirm and AfterDelConfirm events are skipped. If you have set off warnings by using the SetWarnings method of the DoCmd object, BeforeDelConfirm and AfterDelConfirm events still occur, but the confirming dialog is skipped."

So, it seems your database is in that state for some reason. It might be you've used code for this somewhere, or it could perhaps be some corruption. Does the OnDelete event of the form fire (just check it with placing a msgbox "on delete" within it...)?

Roy-Vidar

 

[RoyVidar]

Oups - where was the refresh button;-)

Roy-Vidar

 

[jer007]

The OnDelete does Fire properly. However, the BeforeDelConfirm is still not firing at all. I've checked all my code for a setwarnings False but it's not there. My options are set to confirm record changes.

What I'm also finding interesting is that there is no confirm delete message.

 

[RoyVidar]

Sorry, I don't understand this. The behaviour is exactly as I would have expected if the warnings where turned off. If they are not, then either I'm missing something obvious (quite possible), or there might be corruption issues.

To check the state of the warnings at execution time, you could try to add:

[tt]debug.print Application.GetOption("Confirm Record Changes")[/tt]

together with your delete code. If the result is -1, it eaquals checked, if 0 unchecked.

(and search your code also for GetOption whith the same argument)

Afraid I can't be of more assistance here. If luceze's code works then great.

Here's one way of dealing with corruption btw (backup backup backup) Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times - marketed as something else, but does resolve corruption in a lot of cases. Either that or try copying all objects to a new database, see if any objects fail.

Roy-Vidar