Stopping access to design view 2

[smada80]

Hi all

I wish to stop access to the design view of all queries so that a user cannot see the SQL logic involved.

I am aware that you can stop users viewing designs by restricting access to menus, but holding the shift key bypasses this so its not suitable.
Also, I know that you can use the user security features to stop certain users from viewing designs, but this is a pain also because it forces you to log on every time you start Access, regardless of whether you are opening a restricted DB or not... besides this is the only reason I would need user accounts.

Is there a way of doing this? What I really want is for the user only to be able to view the front end (forms) and nothing else.

I'd appreciate any help.

Thanks
Matt

 

[DJN]

You can create a front end with forms,queries, reports and modules and a back end with tables. Go to tools, database utilities, make MDE file. Make sure you have a back up copy. If you are unsure, have a look at the help and also the Microsoft Knowledge base.

 

[annsolomon]

Set up user security including a separate workgroup information file (i.e., don't use the default system.mdw file). Have your users enter the db via a shortcut on their desktop that includes the workgroup info file in the Target. This will automatically join the user to your workgroup file, have them log in, then automatically returns them to their system.mdw workgroup when they exit the db. If you do it this way, you will NOT have to log in to every single database.

If you want to disable the Shift key, take a look at the AllowBypassKey function. Ann

 

[smada80]

Thankyou both for your help.

Ann,

I have studied the help file on AllowBypassKey property and must admit I am struggling to follow it!!

Do you have an example?

Thanks
Matt

 

[JeremyNYC]

Matt,

There's code to do that in the Access security FAQ. I've got a copy on my website, or you can get it from MS.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[annsolomon]

PLEASE, PLEASE, PLEASE, make a copy of your database before doing anything with this.
======================================================

Public Sub AllowByPass(bTrueOrFalse As Boolean)
Dim db As DAO.Database
Dim prop As DAO.Property
On Error GoTo HandleError
Set db = CurrentDb
db.Properties("AllowByPassKey&quot = bTrueOrFalse
ExitHere:
Exit Sub

HandleError:
If Err = 3270 Then 'Property not found
Set prop = db.CreateProperty(Name:="AllowByPassKey", _
Type:=dbBoolean, Value:=bTrueOrFalse)
db.Properties.Append prop
Resume Next
Else
Resume ExitHere
End If

End Sub

Public Function ByPassKeyStatus() As String
Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next
If db.Properties("AllowByPassKey&quot = True Then
ByPassKeyStatus = "AllowByPassKey is on"
Else
ByPassKeyStatus = "AllowByPassKey is off"
End If
End Function


I put this code behind the DoubleClick event of a transparent button on my main form so I can get back in:
CurrentDb.Properties("AllowBypassKey&quot = True


By the way, I wouldn't have thought of this by myself. I read in several months ago in the VB-Access-SQL Advisor magazine. Ann

 

[annsolomon]

Sorry, Jeremy. Guess we cross-posted. I didn't realize there was a FAQ out there but I'm certainly going to see if I can use it to make my code better. Thanks. Ann

 

[JeremyNYC]

Ann,

No worries. It's just the normal security FAQ, I assume you've seen it? In any case, your code seems pretty decent. I just use what's in there because I stole from MS so long ago and haven't had problems with it.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[smada80]

Excellent, thank you all.