How do i secure Access databases across versions?

I have 2 new MS Access databases that I created using Access 2010.

1.  I need to make 1 of them available to users who may be using versions anywhere from 2000 to 2010.  They need to be able to view the information and manipulate it, as in searches and filters and sorting, but not be able to add, delete. or modify any information.  I have to be able to add, edit and modify.

2.  The 2nd database needs the same restrictions as the first but should only be viewable by a select group of people.

Any ideas on how I could secure these databases?  Do I need to limit the Access versions that people will be able to use?

Thanks in advance!
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you must support users running Access 2000 forward, you must be VERY careful to NOT use any new features that have been implemented since Access 2000 - assuming, that is, that you wish to maintain only 1 "version" of the application. If you plan on issuing different databases for each version of Access, then of course you can provide specific enhancments to each of those databases based on the new features that were introduced in each version.

Needless to say, this would be incredibly difficult to maintain, since ANY enhancements you made would have to be applied to all databases. That would be a maintenance nightmare.

IMO, you would be far better served if you required your users to run a specific version of Access (perhaps 2007 or 2010). This would significantly decrease the maintenance issues you'll face. That said, you may not be able to do this, and if so then you've got to plan carefully and take great care to NOT use any of those new features (like Attachment datatypes, multivalued fields, the Printer object, builting PDF printing, etc etc).

As to security - you could deploy that to a specific folder that ONLY that group can access. Using Windows permissions, you could tightly control who could even get to the file. Of course, a member of THAT group could always grab a copy and plonk it down somewhere else, where others could get to it. But that's an employee training issue, not a software one.
I hope that the new databases you have created are mdb files and not Accdb files.
If they are Accdb files no-one except for A2010 users will be able to use them.

This is a complex issue so I think before going any further we should just get your confirmation on the above point.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

fabi2004CIOAuthor Commented:
I created accdb files.  It sounds like I need to make sure my users are running Access 2007 minimum because I did use some of those new features.

The reason I don't think Windows/AD security will work for me is because I have to limit what the people who access the database can do with it (i.e. not change any data), so folder permissions aren't going to do the trick.
There is a fundamental principle in development which is that you must develop the application using the lowest (oldest) version of the program that your users will be using.
This is because, generally speaking, versions are forward compatible, but not backwards compatible.
I would take your current Access app and try to run it on any A2007 machine and see if it even opens on that machine.

As for the security issue, this ia now a big problem for developers, because basically the features that remain in Access will only keep out honest people.   If data security is important and not just a nice-to-have then Access is not really equipped any more to offer this and you should really be using a server database as the backend.

If you want to identify application users by a username that is not their Windows/network name then you will have to build your own login mechanism.  And whichever way you identify the user, if you want different users to see/do  different things you will have to build this into your application yourself.  You do need to consider Activedirectory settings to limit who can get to the folders containing the database files.

Access 2010 gives you the ability to hide many things, such as the navigation pane and standard  menus/ribbons but anything which is user dependent you will have to build.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Yes, it would seem that you need 2007 at a minimum. Users running 2003 or prior would not be able to use your app.

I agree 100% with Peter regarding the security aspects of 2007. Earlier versions had User Level Security, and while it wasn't perfect it was a lot beter than nothing (which is what we have now).

Peter's Software (no affiliation with our own peter57r) has a product that might help: (Light Application Security). It is, basically, a login system that allows you to direct users to items they can use. It is certainly no replacement for true security, but might suit your needs. Of course you can build your own as well.

Note too that SQL Server Express has a very robust security mechanism, and SSE works very well with Access. However, SSE is "data only", so you'd still need to manage your user-type security on your own (i.e. if UserA can open Form1 but NOT Form2, you'd have to build that logic into the system).
fabi2004CIOAuthor Commented:
hmmm, I didn't think it would be this complicated.  I thought that I could limit user's access to the database via AD/folder permissions.  But I need to make the data "read only" so they can look at it and manipulate it but not change it.  For some reason, I thought Access had features that allowed that.  I was thinking along the lines of form permissions/settings?  Am I completely off base?

I have MS SQL Server 2000 that I could store the data in, but I'd still need a front-end for it.  
Access mdb files have user level security. It is not available in accdb files.
fabi2004CIOAuthor Commented:
Alright, I'm going to close this question and open an new one leaving out the "versions" issue.

Thank you all.
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.