quoting and posting here
I have this code that stops the user from manually deleting the table objects. that it needs vba to delete the table object. Knowing that, I thought that maybe there is also this code that will stop manual open of database tables.
I have attached in here the code that stop manual delete of table. maybe someone know how to modify or know how to stop manual open of table.
'=================================
I have seen a number of post where members have asked how to regain a table after they have accidentally deleted it. I am sure each of us in time past might have deleted a table accidentally ourselves to..... well i know at least I have.
So here is a solution that will stop an access table from being deleted manually by pressing the delete button on the keyboard by accident or otherwise (sabotage). The only way to delete the table is by using DDL with VBA as example 1 shows or using DDL only as a query object.
e.g
Code:
currentproject.connection.execute "DROP TABLE YourtableName"
This code only works if you have an autonumber field in your table and the autonumber field does not necessarily have to be a primary key as well. For those of us who use the Surrogate Key approach (Autonumber's as primary keys) will have no problem adopting this into their applications.
What will make this code stop working?
* If you are developing an database and you run the code to stop the manual deleting of tables everything will work find. But when you split the database (FE & BE) the code stops working and you can go back to manually delete any table. However, this is not a problem but more of a point to note. Because all you have to do is run the code again but this time in the backend (BE) and everything is find again i.e the user cannot delete the tables manually.
* If you run the code in your current database but thereafter you import your tables into a new database the code becomes void in the new database and will not work. Therefore you will have to re-run the code in the new database with the old tables for it to work again.
How to use this Code!
* 1. Copy and paste the code into a new module in your database and save it.
* 2. Press (CTL + G) to open the immediate window.
* 3. In the immediate window you must enter the following ?StopManualTableDelete("Yes") and press enter on your keyboard to DISALLOW a user from manually deleting a table:
* 4. If you want to RE-ENABLE a user to delete the tables again run the following ?StopManualTableDelete("No") in the immediate window:
There is all the code to go into the module.
Code:
'
'---------------------------------------------------------------------------------------
' Procedure : StopManualTableDelete
' DateTime : 5/26/2008 08:05
' Author : Dane Miller - Dallr
' Purpose : Created to stop a person from Manually deleting a table
' Arguments : "Yes" OR "No"
' References: Microsoft ActiveX Data Objects x.x Library
' : Microsoft DAO x.x Library
' Returns : N/A
'---------------------------------------------------------------------------------------
'
Public Function StopManualTableDelete(YesOrNo As String)
Dim fld As DAO.Field
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim SQL_CreateConstraint As String, SQL_DropConstraint As String
Dim strConstraint As String ' this variable holds the name of the constraint
Dim i As Integer
Dim tblNames As String, DeleteInfo As String
Set db = CurrentDb()
i = 0
For Each tbl In db.tableDefs
' Bypass system tables with autonumbers
' Also any hidden table that starts with "~"
If Mid(tbl.Name, 1, 4) <> "MSys" Then
If Left(tbl.Name, 1) <> "~" Then
For Each fld In db.TableDefs(tbl.Name).Fields
If dbAutoIncrField = (fld.Attributes And dbAutoIncrField) Then 'Find autonumber
DoCmd.Hourglass True
strConstraint = "con_" & fld.Name & "_" & tbl.Name 'Build constraint name
If YesOrNo = "YES" Then
i = i + 1
'Drop any existing autonumber field constraints if there is one.
If FindCheckConstraint(strConstraint) = True Then
SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _
" DROP CONSTRAINT " & strConstraint
CurrentProject.Connection.Execute SQL_DropConstraint
End If
DoEvents ' await a while just in case
'create the new constraint to disallow the table from being deleted.
SQL_CreateConstraint = " ALTER TABLE " & tbl.Name & " ADD " & _
" CONSTRAINT " & strConstraint & _
" CHECK (" & fld.Name & " IS NOT NULL))"
'Debug.Print SQL_CreateConstraint
CurrentProject.Connection.Execute SQL_CreateConstraint
DeleteInfo = "CANNOT"
End If
If YesOrNo = "NO" Then
'Drop any existing autonumber field constraints.
If FindCheckConstraint(strConstraint) = True Then
i = i + 1
SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _
" DROP CONSTRAINT " & strConstraint
CurrentProject.Connection.Execute SQL_DropConstraint
DeleteInfo = "CAN"
End If
End If
tblNames = tblNames & tbl.Name & vbNewLine
Exit For
End If
Next fld
End If
End If
Next tbl
db.Close
Set db = Nothing
DoCmd.Hourglass False
If i > 0 Then
MsgBox i & " tables have been set so they " & DeleteInfo & " be deleted manually. " _
& vbNewLine & "These tables are:" & vbNewLine & vbNewLine & tblNames
Else
MsgBox "There are no tables with Autonumber fields present in this database." _
& vbNewLine & "Therefore this code did not have any effect on this database."
End If
End Function
Code:
Public Function FindCheckConstraint(MyConstraint As String) As Boolean
'this function checks to see if a check constraint already exist on the autonumber field.
Dim fld As ADODB.Field
Dim rst As ADODB.Recordset
Set rst = CurrentProject.Connection.OpenSchema(adSchemaCheckConstraints)
Do Until rst.EOF
For Each fld In rst.Fields
If fld.Name = "CONSTRAINT_NAME" Then
If fld.Value = MyConstraint Then
'Debug.Print fld.Value
FindCheckConstraint = True
Exit For
End If
End If
Next fld
rst.MoveNext
Loop
End Function
