User-level security-workgroup security in Access 2016

Question

I inherited quite a few older Access 2007 databases that still utilize user-level security via a security workgroup file.   File formats are *.mdb.   I’ll soon be migrating to Access 2016, and in early testing have been able to successfully join our existing workgroup file using Workgroup Administrator.    As Scott McDaniel, Access MVP, pointed out, we can continue to use the user-level security/Workgroup security in 2016 as long as we keep *.mdb format (see URL below).   I was just wondering what some members of the MS Access community thought about the continued use of user-level security in Access 2016.  I realize it depends on organization, users, etc., but was just hoping for some general comments, thoughts.  OK to do? Terrible idea? 

Also, if I simply convert Access 2007 databases to 2016, is there suggested workflow on how to handle linked tables.  In other words, does it matter if I convert a database to 2007-2016 file format (*.accdb) and keep links to tables that are contained in *.mdb databases?

Appreciate any comments, suggestions, etc.  Thank you!

https://support.office.com/en-us/article/What-happened-to-user-level-security-69b362cd-503f-4e8a-a735-fb923ffb9fa3?ui=en-US&rs=en-US&ad=US&fromAR=1

Answer 1

I don't really see a problem with it. My concern would be that, moving forward, MSFT would stop supporting it in newer versions of Access. Right now you have the time to move at your own pace to replace ULS. If you're forced to do so by a change in Access, you could be in for some problems.

What are you using ULS for?

If you're using it for data security, then I'd suggest moving to a more secure database system, like SQL Server, and using the security mechanisms of that platform to manage security. That can sometimes require some serious restructuring depending on your coding style and data usage policies. If you haven't followed proper client-service techniques - for example, if you base your forms on Tables - you would have to make changes to only pull the information you need, based on user input. For more information on doing this see Armen's excellent article titled The Best of Both Worlds: Access-SQL Server Optimization.

If you're using it for user navigation - like UserA can open FormB, but not FormC - then consider creating your own form of user navigation (or use Peter's Software excellent LASsie system).

Hans has answered your linked table question, so I won't repeat his excellent advice.

Answer 2

If you convert a database from .mdb format to .accdb format, linked tables that link to a .mdb backend will keep on working. You may eventually want to convert the backend from .mdb to .accdb too; you will then have to relink the tables using the Linked Table Manager.

Answer 3

HansV,  thank you so much for your response.  Appreciate the help/answer!

Answer 4

Scott,

Thank you so much for the comments and suggestions.  Very helpful.  Thank you.  We've essentially used ULS to control what individual users, and/or groups of users, can do in specific databases.   Specific permissions to some or all objects in a database depend on the group that the individual user is assigned to.  So, based on your recommendations, we may want to move towards more secure system.  

I'll definitely take a look at the articles that you suggested I read.  Thanks again!

Answer 5

You might want to check out my blog on Login Security with VBA (see below for blog address). It shows ways you can simulate ULS using VBA.