Upgrade ms access 2000/2003 Application with Security group file to a more recent ms access version

I don't really know much about ms access. However, we have a MS access application running on office 2000/2003. We need to upgrade this application to a more recent
  MS access version.  This application runs with a security work group file (mdw). What will be the recommended MS access version
  to convert to while keeping the security file?  

thanks.
ShenAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
To keep the user level security, the DB will need to remain in the MDB format.   If you convert to the ACE format, all the security will be stripped out.

Without more details (how the app is built) my recommendation would be to upgrade to A2010, as this was the last "full" version of Access before a lot changes were made and features dropped.

Jim.
0
 
Mark EdwardsChief Technology OfficerCommented:
I'm sure you probably know this already, but you can RUN a 2000/2003 user-level Access file in any later version of Access, but ONLY Access versions 2003 and earlier have the user-level security management feature and can support the CREATION of mdw files.

However, you can use VBA to create your own mdw users and groups management processes in ANY version of Access, you just can't CREATE and ATTACH an mdw file to a later version.

That said, there are 3rd party apps out there that claim to be able to setup user-level security in Access versions 2007 and later, although I haven't used them.

Hope this helps....
0
 
mbizupCommented:
Just tossing out the recommendation to upgrade to .accdb format - Access 2010 or higher, and reworking the user level security.  There are low cost applications out there that can fairly easily be integrated into your application.  This is just one example:

http://www.peterssoftware.com/las.htm  

My recommendation to scrap the mdb format is based on my organization's security policies, which are now starting to block older version Office files (your organization, of course may be different).
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark EdwardsChief Technology OfficerCommented:
When you click on mbizup's link, if you get a "Webpage Not Found" error like I did, just copy and paste the URL into your browser address bar.  You should get the right page.
0
 
mbizupCommented:
Thanks, Mark

I had tried using EE's link feature to post that with descriptive text... but it didn't work as planned :-)
Anyhow, the link without using that formatting feature is:

http://www.peterssoftware.com/las.htm
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<However, you can use VBA to create your own mdw users and groups management processes in ANY version of Access, you just can't CREATE and ATTACH an mdw file to a later version.>>

  Actually you can.   Starting with A2007, a command was added for starting up the workgroup admin utility.    It's just not in the UI anymore, so you need to call it from code or create a macro to call it:

https://support.microsoft.com/en-us/help/918583/how-to-use-the-workgroup-administrator-utility-in-access-2007

 and the /wrkgrp shortcut still works as well.

  User Level Security aside, there is possibly much more to this.    If the app is an ADP, uses DBF support, replication, etc, then A2010 will be the latest release they can easily move to.    A2013 depreciated a number of features:

https://support.office.com/en-us/article/Discontinued-features-and-modified-functionality-in-Access-2013-BC006FC3-5B48-499E-8C7D-9A2DFEF68E2F

  and one of those means having to deal with the ribbon instead of menus.

  So A2010 would be an easy switch, but anything beyond that will require some effort.

Jim.
2
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and one other note on A2007 and up; Microsoft never really removed ULS with the ACE format.   All they do is strip out the ACL's in the DB and set everything back to the default of an un-secured DB.

  There is still a work group file (SYSTEM1.MDW), and you still can mess up security if you point to anything other than that (and you can do that<g>).

Jim.
1
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
One other thing just hit me too; if ULS is being used, hopefully they have all the settings.   If they go to A2013 and up, they will need to create a new workgroup file if the current one is in JET 3.x format.   A2013 won't be able to read that and they'd have to create a new one (or un-secure the DB and then secure it back again).

 But let's let Shen fill in some more details before we get into all that.

Jim.
1
 
Mark EdwardsChief Technology OfficerCommented:
Jim.  Thanks for the update, especially the depreciated items list.  There are a lot of folks still using earlier versions of Access, like 2003, that have capabilities that you can't find in later versions, just for that reason.  If Microsoft wants folks to upgrade to the latest version of something, then taking away a feature that is used in some big-time solutions, like commercially sold Access applications to meet government requirements for the securities and biopharma industries, is not smart.  ULS was a big one, and if the company needs ULS and can't/won't use SQL Server, etc. (I use to work for one), then they are stuck nursing these old apps along.
0
 
PatHartmanCommented:
If you haven't read this, you should.  It also tells you how to remove the security if you elect to do that.

If you are upgrading, you might not have a choice as to which version you go to.  I also prefer A2010 to the newer versions.  However, you can't buy it from Microsoft unless possibly you have one of the blanket agreements.
SECFAQ.doc
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
@ Mark
"that have capabilities that you can't find in later versions"
For example (just curious) ?
0
 
Mark EdwardsChief Technology OfficerCommented:
@dbMX: All you got to do is look at what's been depreciated over the years.  I use to work for a company that to this day still maintains and sells their Access 2003 user-level secure apps to the biopharma industry to meet CFR 21 Part 11 government regulation requirements.  It's a light-weight app and the clients rarely go over a few thousand records, so they don't want to mess with SQL Server, but they need the government regulation features of the apps (it has high marks from the FDA).  As long as A2003 files work in later versions of Access, they're going to keep it in A2003.  They also include the Access 2010 runtime in their installation packages (A2010 is as late as they are going to go, but user's can use whatever version they have as long as it's A2003 or later).

Another is the email forms that you can send out by email and process on return that got killed in A2003.  Know anything else that will do that without spending a tidy sum?

I hope others will add their 2-cents to this post.  I'd love to hear what other developers who have been stymied by old versions of Access have to say, and why they have to keep using them.

Sometimes a think Microsoft kills features so it won't intrude on someone else's  game, or some other ill-thought reason.
0
 
Mark EdwardsChief Technology OfficerCommented:
sorry, the email form got killed in A2013.... my bad.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Another is the email forms that you can send out by email and process on return"
Not sure what you are referring to ?
Must be something I have forgotten
0
 
Mark EdwardsChief Technology OfficerCommented:
0
 
Mark EdwardsChief Technology OfficerCommented:
Another one just came to mind.  The depreciation of web apps.  There were a lot of developers who began specializing in it and basing their careers and fortunes on Microsoft steadily improving on web apps.... then MS goes and kills it.

There was a lot of fire-and-brimstone directed at Microsoft in the forums for that one.  Julian Kirkland is still hunting the jackal that made that decision with his machine gun  :-)

But, I don't blame MS for killing the ADP.  You can do much better with a regular .accdb and a mix of ADO as well as DAO.  That's what the drawback to ADPs was - no Access tables.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well Web Apps were mainly 2013, not prior.
Web Databases were 2010.
Yes ... we've seen Julian with his machine gun :-)
Meanwhile ... enter PowerApps. Not a specific replacement for Web Apps, but appears to be the future. I've built a couple here at work.
0
 
ShenAuthor Commented:
Thank you for all the suggestions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.