Weekly user import

[jonefer]

I have a table which should suffice as UserID's and Roles (2 columns). This
table would need to replace the existing list in the MDW on a weekly basis.

What is the best way to import my table and be able to assign values in to
roles in Access for the mdw?

 

[Scott McDaniel]

I have a table which should suffice as UserID's and Roles (2 columns). This
table would need to replace the existing list in the MDW on a weekly basis.

What is the best way to import my table and be able to assign values in to
roles in Access for the mdw?

Users are stored in the mdw file. If by "roles" you mean object permissions, then those are stored in the database
itself. AFAIK, there is no way to update either of them via an import, unless you use VBA code to import the users to
the mdw (i.e. Add a user to the security file, if needed), then add user permissions in the database file (again via
VBA).

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[jonefer]

Yes, I was planning to use VBA code to update either of those. If you open
up the MDW file you see a table listing the user names. I was wondering how
to go about writing such a loop to import into that table and set the
permission for each user one at a time (VBA)

The database is unconventional as it is completely refreshed with new data,
as well as new users, - the story about why this is is a long one.

 

[Scott McDaniel]

Yes, I was planning to use VBA code to update either of those. If you open
up the MDW file you see a table listing the user names. I was wondering how
to go about writing such a loop to import into that table and set the
permission for each user one at a time (VBA)

While you might be able to just import the table, I've never done so and therefore don't know the issues you might face
when doing this ... in most cases, you'd use VBA to add a user to the .mdw file, then set permissions on that user for
the various objects/tables etc.

I think you'd be wise to setup Groups in your .mdw file, then add users to the relevant Group when adding them ... you
can then manipulate permissions on the Group instead of on specific users. Of course, if you require that much
granularity in your permissions, you may need to do it on a user level.

The User Level Security FAQ has some code that will get you started; there are other resources as well, including the
Access Developer's Handbook from Sybex (I believe the last version was for Access 2002, but the code samples would be
relevant for any version of Access from 2000 forward).

Security FAQ:
http://support.microsoft.com/?id=207793

Garry Robinson's book on Security:
www.vb123.com/map

The Jet Databse Engine Programmer's Guide (Microsoft Press) also has some code samples. It's out of print, but you can
pick it up at any of the online booksellers pretty cheap.

The database is unconventional as it is completely refreshed with new data,
as well as new users, - the story about why this is is a long one.

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com