Removing database window from ADE version of project

[BSman]

I have developed many applications using an Access front end and a SQL Server back end. Recently I went back to trying Access Project for an application I need for myself (and some other IT people). A concern I have for using Access Project for general use is that I can't figure out how you would develop enough security for the project even when it was turned into an ADE project for production use. My concern is that a user could hold the shift key down and view the database window. It's a lot more dangerous with Project for the simple reason that with the database window the user can not only look at and change data in tables, but can modify tables, views, stored procedures, etc. I'd like to make it impossible for a user to be able to even view the database window when in an ADE project. Does anyone have any suggestions? I know that some control can happen using domain security, but I would like to eliiminate temptation by making it impossible for users to even see the database window.

Bob

 

[ajrimmer]

The key here is to disallow special keys & shift on startup. To do this I have a form (visible to the sa/me only) with the following code & two tick boxes to allow/disallow the two options:

Private Sub cmdUpdateKeys_Click()

   Dim dbs As CurrentProject
   Dim prp As ADODB.Property
   Dim strTitle As String

   Const INVALID_PROPERTY_REFERENCE As Integer = 2455

On Error GoTo ErrorHandler

   Set dbs = Application.CurrentProject
   strTitle = "Setting ADP Startup Options"

   'Try to set the property, if it fails, the property does not exist.

   dbs.Properties("AllowBypassKey") = Me.chkShiftBypass.Value
   dbs.Properties("AllowSpecialKeys") = Me.chkShiftBypass.Value
   MsgBox "Restart PEaT for option to take effect", vbOKOnly + vbInformation, "Option Set"
   
ExitLine:
   Set dbs = Nothing
   Set prp = Nothing
   Exit Sub

ErrorHandler:
   If Err.number = INVALID_PROPERTY_REFERENCE Then
      ' Create the new property.
      dbs.Properties.add "AllowBypassKey", strTitle
      dbs.Properties.add "AllowSpecialKeys", strTitle
      Resume Next
   Else
      Resume ExitLine
   End If

End Sub

Obviously, untick 'show database window' in the startup options. Most important - make a backup to ensure it all works and you can re-enable these before disallowing keys/shift or you might hide the database window permanently.

You might want to have a look here:

http://msdn2.microsoft.com/en-us/library/aa140020(office.10).aspx

for some more information (which is where I started) - although marked for A2000, I'm using A2003 & it works well. HTH.

 

[BSman]

Thanks. This looks like a solution, but continues with the problem that there is no way to undo it for the adb file, so you have to be sure to always disallow the shift and other keys on a copy of the application. Of course, the problem with that is that there's going to be that "one time" that you forget to go through the copy/backup procedure before turning off the startup options.

However, in looking at an ADE file, it is as I feared. You can design/modify tables as well as play around with other SQL objects. At least with an Access database you can't do any changes/damage to linked tables (even in the MDB), whether the links are to an Access back end or an SQL (or Oracle) back end.

Bob

 

[danvlas]

You can change server objects(tables, views, stored procs and so on) through the 'database window' ONLY if you have appropriate rights on the server.

If you employ proper security on the server (no rights whatsoever on tables, only Select rights to the appropriate groups for views, action stored procedures only available to appropriate groups) then the database window of Access becomes pretty unusable - no tables are seen, no changes can be made to views/stored procs and so on.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html