469,312 Members | 2,503 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,312 developers. It's quick & easy.

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

anoble1
235 100+
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.
Aug 8 '11 #1

✓ answered by NeoPa

I don't think you need anything so cumbersome.

The following code makes the database window visible (and selects the Forms tab) :
Expand|Select|Wrap|Line Numbers
  1. 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.

12 9696
NeoPa
32,173 Expert Mod 16PB
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.
Aug 8 '11 #2
anoble1
235 100+
@NeoPa, found out how to do it!!!
Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim Prop As Property
  3.  
  4. Const conPropNotFound = 3270
  5.  
  6. Set db = CurrentDb()
  7. db.Properties("AllowSpecialKeys") = False
  8. db.Properties("StartupShowDBWindow").value = False
  9. db.Properties("AllowFullMenus").value = False
  10. db.Properties("AllowBuiltinToolbars").value = False
  11. 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?
Aug 9 '11 #3
NeoPa
32,173 Expert Mod 16PB
I don't think you need anything so cumbersome.

The following code makes the database window visible (and selects the Forms tab) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 10 '11 #4
anoble1
235 100+
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.
Aug 10 '11 #5
NeoPa
32,173 Expert Mod 16PB
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.
Aug 10 '11 #6
anoble1
235 100+
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
Aug 10 '11 #7
neelsfer
547 512MB
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?
Expand|Select|Wrap|Line Numbers
  1. Function ap_disableshift()
  2. 'This function disable the shift at startup. This action causes
  3. 'the Autoexec macro and Startup properties to always be executed.
  4.  
  5. On Error GoTo errDisableShift
  6.  
  7. Dim db As DAO.Database
  8. Dim prop As DAO.Property
  9. Const conPropNotFound = 3270
  10.  
  11. Set db = CurrentDb()
  12.  
  13. 'This next line disables the shift key on startup.
  14. db.Properties("AllowByPassKey") = False
  15.  
  16. 'The function is successful.
  17. Exit Function
  18.  
  19. errDisableShift:
  20. 'The first part of this error routine creates the "AllowByPassKey
  21. 'property if it does not exist.
  22. If Err = conPropNotFound Then
  23. Set prop = db.CreateProperty("AllowByPassKey", _
  24. dbBoolean, False)
  25. db.Properties.Append prop
  26. Resume Next
  27. Else
  28. MsgBox "Function 'ap_DisableShift' did not complete successfully."
  29. Exit Function
  30. End If
  31.  
  32. End Function
  33.  
  34. Function ap_EnableShift()
  35. 'This function enables the SHIFT key at startup. This action causes
  36. 'the Autoexec macro and the Startup properties to be bypassed
  37. 'if the user holds down the SHIFT key when the user opens the database.
  38.  
  39. On Error GoTo errEnableShift
  40.  
  41. Dim db As DAO.Database
  42. Dim prop As DAO.Property
  43. Const conPropNotFound = 3270
  44.  
  45. Set db = CurrentDb()
  46.  
  47. 'This next line of code disables the SHIFT key on startup.
  48. db.Properties("AllowByPassKey") = True
  49.  
  50. 'function successful
  51. Exit Function
  52.  
  53. errEnableShift:
  54. 'The first part of this error routine creates the "AllowByPassKey
  55. 'property if it does not exist.
  56. If Err = conPropNotFound Then
  57. Set prop = db.CreateProperty("AllowByPassKey", _
  58. dbBoolean, True)
  59. db.Properties.Append prop
  60. Resume Next
  61. Else
  62. MsgBox "Function 'ap_DisableShift' did not complete successfully."
  63. Exit Function
  64. End If
  65.  
  66. End Function
  67.  
  68.  
Aug 10 '11 #8
neelsfer
547 512MB
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".
Aug 10 '11 #9
anoble1
235 100+
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

Expand|Select|Wrap|Line Numbers
  1. AllowSpecialKeys
Aug 10 '11 #10
NeoPa
32,173 Expert Mod 16PB
@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.
Aug 11 '11 #11
anoble1
235 100+
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!
Aug 11 '11 #12
NeoPa
32,173 Expert Mod 16PB
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.
Aug 12 '11 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

10 posts views Thread by Job Lot | last post: by
5 posts views Thread by Santiago Ordax Solivellas | last post: by
3 posts views Thread by Random Person | last post: by
46 posts views Thread by Adam Turner via AccessMonster.com | last post: by
reply views Thread by | last post: by
4 posts views Thread by xperre van wilrijk | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.