How to Restrict access to tables in MS access if they are not an admin

[anoble1]

Hi,

I have a question. I want to make my program where if your username = "asdf" then you can view the MS Access tables/querys etc.

Is this even possible? I have another database where it is always blocked, and the only way you can view them is to hold the "SHIFT" key when clicking on the program. I don't really like that way though.

[NeoPa]

I would suggest protecting the database in whichever way seems appropriate, then your code, on opening of the database, would check the NTID and make the Database window visible if it is found to be within a list (or table) of acceptable users.

[anoble1]

@NeoPa, found out how to do it!!!

Dim db As Database
Dim Prop As Property
 
Const conPropNotFound = 3270
 
Set db = CurrentDb()
db.Properties("AllowSpecialKeys") = False
db.Properties("StartupShowDBWindow").value = False
db.Properties("AllowFullMenus").value = False
db.Properties("AllowBuiltinToolbars").value = False
Set db = Nothing

But, the problem is you have to restart Access when you change the AllowSpecialKeys properties on the Application object. Any way around that? Or tricks?

[NeoPa]

I don't think you need anything so cumbersome.

The following code makes the database window visible (and selects the Forms tab) :

Call DoCmd.SelectObject(ObjectType:=acForm, InDatabaseWindow:=True)

If the database window starts as hidden anyway, you should be able to use this in your logic if, and only if, you have deteremined that the user is allowed to see it.

BTW. F11 will show the database window as long as SpecialKeys are allowed.

[anoble1]

Yeah, I guess I can do that. It will just be like: "It will keep a honest person honest way" cause I can't get the other way to work.

[NeoPa]

I wish I understood better what you're trying to say. You don't really specify even what level of security you're after, and as you technical understanding is somewhat limited I have very little even to infer from.

If there is something specific you want help with doing, explain it clearly and I'll do what I can to help.

[anoble1]

Sorry for not being specific. What I am "trying" to shoot for is when a user (Anyone who is not me, and 1 other person) if they try to Edit or view my code to make it where they cannot access it by rightclicking or going up to the options and clicking on Visual Basic. Also, I would like to block it if they try to hold down the SHIFT key on the keyboard when clicking on the icon. I am trying to make it to where there is no way they can see my tables or anything except for the program in a nutshell

[neelsfer]

i use the following code in a module to block the shift function. It comes from the Microsoft website.
Sorry to hijack your post but i have similar issues.
I would also like to restrict anybody from seeing my tables and queries. Your current db tables and queries can be imported from an accde Access application, into another Access DB.Is it possible to hide the table & queries from being imported?

Function ap_disableshift()
'This function disable the shift at startup. This action causes
'the Autoexec macro and Startup properties to always be executed.
 
On Error GoTo errDisableShift
 
Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270
 
Set db = CurrentDb()
 
'This next line disables the shift key on startup.
db.Properties("AllowByPassKey") = False
 
'The function is successful.
Exit Function
 
errDisableShift:
'The first part of this error routine creates the "AllowByPassKey
'property if it does not exist.
If Err = conPropNotFound Then
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, False)
db.Properties.Append prop
Resume Next
Else
MsgBox "Function 'ap_DisableShift' did not complete successfully."
Exit Function
End If
 
End Function
 
Function ap_EnableShift()
'This function enables the SHIFT key at startup. This action causes
'the Autoexec macro and the Startup properties to be bypassed
'if the user holds down the SHIFT key when the user opens the database.
 
On Error GoTo errEnableShift
 
Dim db As DAO.Database
Dim prop As DAO.Property
Const conPropNotFound = 3270
 
Set db = CurrentDb()
 
'This next line of code disables the SHIFT key on startup.
db.Properties("AllowByPassKey") = True
 
'function successful
Exit Function
 
errEnableShift:
'The first part of this error routine creates the "AllowByPassKey
'property if it does not exist.
If Err = conPropNotFound Then
Set prop = db.CreateProperty("AllowByPassKey", _
dbBoolean, True)
db.Properties.Append prop
Resume Next
Else
MsgBox "Function 'ap_DisableShift' did not complete successfully."
Exit Function
End If
 
End Function
 
 

[neelsfer]

i have just come across this tip when searching the net, on how to hide the tables. Will try it later.
"The easiest and quickest way to prevent imports from the front is to mark the linked tables as hidden, then set Show hidden objects to False".

[anoble1]

Yeah, the unction I used seems to work really well, but ACCESS has to restart for it to work correct. Nice bypass, I'll have to get the other to work first so I don't lock myself out lol

AllowSpecialKeys

[NeoPa]

@Neels
You are welcome to read and benefit from anything ANoble1 gets in this thread. You are also (more than) welcome to offer any contributions that may seem helpful. What you will not be able to do is explain your situation here and request help in this thread. If there is anything you need which is specific to you then you must post it as a separate thread, though it is perfectly acceptable to include a link to this thread in yours.

@ANoble1
I suspect what you need is not directly related to the bypass key at all. That seems to me entirely unrelated (It may need to be set but not in code ever). The code needs to decide whether or not to show the Database Window depending on whether or not the user is one of you or your specific colleague. The point here is that the shift key need not be enabled in any circumstances. Only the code manages showing the Database Window. That scenario was already covered in my post #4. It seems you misunderstood some of the implications possibly.

Code is another matter of course. There are (various) ways of blocking access to code. One of the simplest is to keep a master MDB or ACCDB copy for yourself and one other, and release an MDE or ACCDE file for the rest. Another is simply to password protect the project.

[anoble1]

Very good. I will explore more about the ACCDB databases and how exactly they work. I miss undeerstood the code for some reason. I'll blame it on the brain fart. That's for #4 and the help you always provide!

[NeoPa]

MDB and ACCDB are the standard databases as designed within Access (2003 and earlier for MDB and 2007 and later for ACCDB). MDE and ACCDE are the ones you may need to look into. They are the ones which are not able to be fiddled with or the design changed.

I'm pleased you found the post helpful. With a question like this there are various right answers. It depends mainly on exactly what you want. Hopefully this gives pointers to some of the ways that suit your requirements. Good luck with your project.