Prevent deleting records in a table in MS Access

[PJname]

Can someone give me an idea or Visual basic coding to prevent record(s) from being deleted in a MS Access table?

I do not want records deleted from a particular table.

Any suggestions or ideas would be appreciated.

 

[eti3nne]

PJname,

Unlike in say Excel, where you can protect an area of a table from changes, there is no equivalent in Access.

You should not allow users direct access to your tables; instead, all data additions and changes should be made via a form.

 

[BNPMike]

Access provides user level security - more correctly access privileges. You can deny users the ability to delete from a particular table if you want. Search help for Security.

 

[willir]

PJ

Users should not see the "tables". Control the interface via your forms. Then, you have the ability to control what the end user can do.

You also have the ability to implement security via Access, but Access security is not perfect.

And you do have a backup plan in place right??

Richard

 

[PJname]

Thanks for the helpful hints.

The users will definitely be updating from a form. I never allow users to update directly to the table.

on the input form I have set the "Allow Deletions" = No


I definitely run backups against the table. Using the following code to do this:

Function backuptable3()
'-----------------------------------------------------------
' This function creates a backup of any table in the database by
' using the copyobject function.
'-----------------------------------------------------------

' Dim PROMPT1 As String
' Dim PROMPT2 As String
Dim newday
Dim TITLE As String
Dim NEWNAME As String
Dim OLDNAME As String
newday = Now()
firstday = Format(newday, "mm/dd/yy hh:nn")
OLDNAME = "Master_tbl_#1"
NEWNAME = "Master_tbl_#1 " & firstday
TITLE = "BACKUP Master_tbl_#1"
DoCmd.CopyObject , NEWNAME, A_TABLE, OLDNAME
End Function

created macro to run this.

I have another question?
Is there a way to disable the page up or page down keys using Visual Basic coding?

Thanks again for your suggestions.....

 

[PJname]

I found the answer to my question above about disabling Page Up or Page Down keys. Found this coding:

'************ Code Start **********
' 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 Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
'33 - PgUp; 34 - PgDown; 9 - Tab; 18=Alt
Select Case KeyCode
Case 33, 34, 9, 18
KeyCode = 0
Case Else
'Debug.Print KeyCode, Shift
End Select
End Sub
'************ Code End **********

Code works like a charm.

 

[BNPMike]

I still recommend you use Security to control deletion.

It's all very well controlling individual forms but the idea of database is if you can declare business logic within the database then you don't need to worry about it being accidentally subverted in client programs. Access/Jet allows you to declare access privileges, so why not take advantage of that facility?

Your requirement is "Don't let Group X delete things". It is not really "Don't let Group X delete things on Form Y".