Dimensioning variables in DisableShiftKeyBypass function

[OssieMac]

Not sure if I should place this question under Security or Programming.

I am using the following code (posted below) directly out of the downloaded
MS Access Security FAQ.doc. (Well the function is but the code in the sub
invoking the function is my code.)

When I compile I get errors on the following 2 lines dimensioning variables.

Dim ws As Workspace
Dim db As DATABASE

Compile error:
User-defined type not defined.

You will see in the code below that I have commented out the As Workspace
and As DATABASE and the code runs and under exhaustive testing, it appears to
do what it should.

It appears that I should be setting a reference or something but I haven't a
clue as to what I should do so any help will be greatly appreciated.

One might ask why I bother if it works but I am still quite new to this
Access programming and knowing the answer it might help in the future.

Option Compare Database
Option Explicit

Sub SetShiftKeyBypass()

Dim strDBName As String
Dim fAllow As Boolean
Dim mySetBypass

strDBName = CurrentDb.Name

fAllow = True

mySetBypass = faq_DisableShiftKeyBypass(strDBName, fAllow)

MsgBox "DisableShiftKeyBypass set to: " & mySetBypass
End Sub


Function faq_DisableShiftKeyBypass(strDBName As String, fAllow As Boolean)
As Boolean

On Error GoTo errDisableShift

Dim ws 'As Workspace
Dim db 'As DATABASE
Dim dbBoolean
Dim prop As Property
Const conPropNotFound = 3270

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(strDBName)

db.Properties("AllowByPassKey") = Not fAllow
faq_DisableShiftKeyBypass = fAllow
exitDisableShift:
Exit Function

errDisableShift:
'The AllowBypassKey property is a user-defined
' property of the database that must be created
' before it can be set. This error code will execute
' the first time this function is run in a database.

If Err = conPropNotFound Then
' You must set the fourth DDL parameter to True
' to ensure that only administrators
' can modify it later. If it was created wrongly, then
' delete it and re-create it correctly.
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False, True)
db.Properties.Append prop
Resume
Else
MsgBox "Function DisableShiftKeyBypass did not complete successfully."
faq_DisableShiftKeyBypass = False
GoTo exitDisableShift
End If
End Function

 

[OssieMac]

Thanks for the prompt reply Chris. That has fixed one problem.

However, I now have another problem with the same code. It ran perfectly in
the FE of the project but refuses to do anything in the BE. It does not
return any message in the msgbox in the sub that invokes the function.

I have set the security up for both the FE and BE using the same mdw file.
The groups and user names are the same. The security/permissions etc appear
to be doing what they are meant to.

As per the instructions I had, I deleted all the links in the FE and then
re-linked them to the BE and set my UsrGrp security with full permissions to
the links in the FE and limited the permissions in the BE.

All the security appears to be working as it is supposed to.

The code I posted, was inserted in a module in the FE and it ran perfectly.

I used a copy of the exact same code in a module in the BE and it does not
work.

No doubt, as before, I am missing something here just when I thought I was
on top of all the security stuff.

Any help will be appreciated.

 

[OssieMac]

Hi Chris,

You seem to have my meaning a little confused but that being the case I must
accept blame for not explaining it well enough so I'll try again.

Firstly in both Front End and Back End I selected Tools->Startup.
I Unchecked Display database window.
I Unchecked Use Access Special keys.

The following was all done by me logged on as the Database Owner.

I copied the code to disable the Shift Key bypass into a module in the Front
End and ran the code. It worked as planned and stopped the use of the Shift
key during startup so the user could not get the Database Window to display.

I closed the Front End and opened the Back End and then I then copied the
exact same code into a module in the Back End and ran the code. It does NOT
work as planned and if the shift key is held during startup then the Database
window displays.

All of my other security appears to work as planned. I'll give a brief
description of what can and can't be done by the user. You might be able to
pick some holes in it.

Both Front End and Back End use the same mdw file. User names and group
names are the same in both as are the passwords.

Neither the Front End or Back End can be opened by double clicking in
Windows Explorer or by opening Access first and then attempting to open them.

Both the Front End and the Back End can be opened by the user from Desk Top
Icons with the Target set up with the appropriate switches to direct it to
the correct mdw file. (Of course I will not install a desktop icon for the
Backend but any user with a little knowlege could copy the icon for the Front
End and edit the file name to the Back End file name.)

Your question "Why do you have startup code in the back end?" I didn't
insert the startup code in the Back End; my understanding is that it is there
by default and if not blocked then it allows the Database window to open. All
I want to do is stop the User from displaying the Database window so they
cannot get directly into the tables so that all data changes must be done via
the forms where validation takes place.

I just don't understand why the code will not run in the Back End when it
runs in the Front End.

In particular you might like to look at the Sub where I invoke the function.
There was no example of that in the documentation and perhaps I have
something wrong there and it is more by good luck than good management that
it worked in the Front End and not in the Back End.

Anyway thanks again for taking the time to help me. It is much appreciated.
I have done extensive programming with VBA in Excel but this is my first
Access project and it has been a very steep learning curve for me.

 

[Keith Wilby]

I just don't understand why the code will not run in the Back End when it
runs in the Front End.

At the risk of asking a stupid question, you did remove the startup options
in the BE didn't you? Another approach if all else fails is to copy your FE
file, delete everything and the import your tables into the empty file.

Regards,
Keith.
www.keithwilby.com

 

[Joan Wild]

At the risk of asking a stupid question, you did remove the startup
options in the BE didn't you? Another approach if all else fails is to
copy your FE file, delete everything and the import your tables into the
empty file.

Except, the permissions won't travel with the import.

OssieMac, when you copy the code into the backend, use Debug, Compile to
check for errors; perhaps a reference is missing in the backend.

 

[Keith Wilby]

Except, the permissions won't travel with the import.

Hmm, perhaps I should stop assuming that everyone uses RWOP queries ;-)

 

[OssieMac]

Hi Keith and Joan,

I'll answer to both of you in this post. I have overcome the problem (not
really solved it because it is a workaround) and managed to disable the shift
key during startup but first in answer to your posts.

Keith's quote:"you did remove the startup options in the BE didn't you?"
Yes. As per the following in my previous post:
Firstly in both Front End and Back End I selected Tools->Startup.
I Unchecked Display database window.
I Unchecked Use Access Special keys.
In addition I also set Display Form/Page: In Front End the Switchboard and
Backend to a form that simply has a message in a label telling the user that
they need to open the the application FE etc. (In testing it appears that if
one is successful in disabling the shift bypass then even the db Owner looses
access to it if a form is not displayed when Display Database Window is
unchecked.)

Keith's quote: "copy your FE file, delete everything and the import your
tables into the empty file."
I created an entire new BE and imported the tables, reset the permissions
and ran the shift key disable code and it failed. Have not tried copying the
FE yet but sounds like a good idea as an experiment and I will try it but as
I have it working now, that will be on the back burner for a while.

Joan's quote: "permissions won't travel with the import". In blundering my
way through all this security stuff I had worked that out but thanks for your
input. Somewhere I also saw that one should delete all the links in the FE
and then re-create the links. I did that also.

Joan' quote: "use Debug, Compile to check for errors; perhaps a reference is
missing in the backend. Have done that. In fact that goes back to my original
post that Chris answered and is now fixed. Also I always use Option Explicit
for the purpose of identifying that tyoe of error.

Anyway I have now fixed the problem by using Albert D. Kallal's little
Utility that I downloaded from:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

It worked first time. (I worked out that to use it it is necessary to open
the secured database as the database owner and then close the database
without closing Access. Then open the utility). So my question now is why
does it work and the other code from MS Access Security FAQ does not work? I
am thinking that it might have something to do with the error call.


Anyway thankyou Chris, Keith and Joan for your contributions. Much
appreciated. No real need for any more replies now I have it working. However
if someone actually identifies the problem then I will be interested.

 

[Keith Wilby]

Anyway I have now fixed the problem by using Albert D. Kallal's little
Utility that I downloaded from:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

It worked first time.

Yes, Albert's utility is brilliant, I use it all the time, I don't know why
I didn't suggest it earlier :-\

Keith.

 

[Joan Wild]

Hmm, perhaps I should stop assuming that everyone uses RWOP queries ;-)

Even so, they'd have to reset the permissions on the tables, unless they had
set the <New tables/queries> permission before importing (usually
forgotten).

 

[Joan Wild]

In addition I also set Display Form/Page: In Front End the Switchboard and
Backend to a form that simply has a message in a label telling the user
that
they need to open the the application FE etc. (In testing it appears that
if
one is successful in disabling the shift bypass then even the db Owner
looses
access to it if a form is not displayed when Display Database Window is
unchecked.)

True, but you can re-enable the property from another database (which is how
Albert's utility works).

It worked first time. (I worked out that to use it it is necessary to open
the secured database as the database owner and then close the database
without closing Access. Then open the utility).

Right, you need to be using the secure mdw, while logged in as a member of
the Admins Group.

So my question now is why
does it work and the other code from MS Access Security FAQ does not work?
I
am thinking that it might have something to do with the error call.

I would suggest you compare your code with Albert's. I haven't looked at
your code, but you may not be creating the property/checking for its
existence before setting it?