By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,299 Members | 1,422 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,299 IT Pros & Developers. It's quick & easy.

Add users to security file from a form

beacon
100+
P: 579
Hi everybody,

I have a database that I intend on setting up user/group permissions for using the built-in security wizard, but I don't want to have to manage adding every user since the database is primarily used by a different department at my company.

I will still end up being the administrator for the database, but I want the users to be able to add new users and assign their permissions, which will be the same for all users, using a form so they won't have to access the security file.

Is this possible?

Thanks,
beacon
Jul 21 '10 #1

✓ answered by munkee

For some reason I couldnt get the sql working when I gave it a go last night although I'm sure this was working for me a couple of years ago.

Your 3 options on controlling the security via vba can be found here:

http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

more direct for using ado which looks quite simple.

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx


I will look in to further why the sql isnt working and post back as it was really easy to utilise in the past.


Edit:


Cracked it I forgot you had to use ADO to be able to use Jet 4 sql commands. Just create the string adding in any elements needed from your form.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdClick()
  2. Dim conDatabase As ADODB.Connection
  3. Dim SQL As String
  4.  
  5. On Error GoTo ErrorsofMassDEATH
  6.  
  7. Set conDatabase = Application.CurrentProject.Connection
  8. SQL = "CREATE USER user1 password1 pid1"
  9. conDatabase.Execute SQL
  10.  
  11. conDatabase.Close
  12. Set conDatabase = Nothing
  13.  
  14. Exit Sub
  15. ErrorsofMassDEATH
  16.    MsgBox Err.Description, vbInformation
  17. Exit Sub
  18. End Sub
  19.  

Share this Question
Share on Google+
3 Replies


100+
P: 374
This is very much possible beacon, to be honest I have found creating my own security system much easier than using asccess built in security.

However with that being you can use SQL to add users / permissions / groups etc.

You will need to simply tie the following sql procedures to execute within unbound forms.

To create an account sql is

CREATE USER user1 password1 pid1 [, user2 password2 pid2 [, etcetcetc] ]

to create a new group

CREATE GROUP group1 pid1 [, group2 pid2 [, etcetcetc] ]

To change a password you can use

ALTER USER user PASSWORD newpassword oldpassword

To add a user to a group you can use

ADD USER user1 [, user2 [, etcetcetc ]] TO group

To remove a user you can use

DROP USER user1 , [, user2 [, etcetcetc ]] FROM group

To add them a priveladge you can use

GRANT privilege1 [, privilege2 [, etcetcetc ] ] ON {TABLE table1 | OBJECT object1 | CONTAINER container1} TO account1 [, account2 [, etcetcetc]]

Where you select a priveledge clause within the {} brackets above

All of the above referenced from access 2002 desktop developers handbook chapter 5 incase you have it and want some more detail.
Jul 24 '10 #2

beacon
100+
P: 579
@munkee
Honestly, I prefer to set up my own security too, but this is a database for a different department that needs something setup quickly and due to demands on my time, I really can't afford to create all of the security for them in the timeframe that they need it.

Just so I'm clear, I can add the SQL to a form in the main database and it will update the security file? I'm assuming that a link is created between the main database and the security file when the security file is created and that I won't have to reference that link if I use the SQL you provided in one of my forms...is that right?
Jul 24 '10 #3

100+
P: 374
For some reason I couldnt get the sql working when I gave it a go last night although I'm sure this was working for me a couple of years ago.

Your 3 options on controlling the security via vba can be found here:

http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

more direct for using ado which looks quite simple.

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx


I will look in to further why the sql isnt working and post back as it was really easy to utilise in the past.


Edit:


Cracked it I forgot you had to use ADO to be able to use Jet 4 sql commands. Just create the string adding in any elements needed from your form.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdClick()
  2. Dim conDatabase As ADODB.Connection
  3. Dim SQL As String
  4.  
  5. On Error GoTo ErrorsofMassDEATH
  6.  
  7. Set conDatabase = Application.CurrentProject.Connection
  8. SQL = "CREATE USER user1 password1 pid1"
  9. conDatabase.Execute SQL
  10.  
  11. conDatabase.Close
  12. Set conDatabase = Nothing
  13.  
  14. Exit Sub
  15. ErrorsofMassDEATH
  16.    MsgBox Err.Description, vbInformation
  17. Exit Sub
  18. End Sub
  19.  
Jul 25 '10 #4

Post your reply

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