I have been working with an old Access 2000 application and decided, due to the change in use to a Kiosk type application, where multiple people use the same application without shutting down, that the security mechanism based on Workgroup security will have to go. This application had previously been secured by using a bespoke workgroup file, where the original Admin user had been deleted and a new Administrator user had been added to act as the overall administrator.
I tried the mechanisms that I found by searching for it, namely to give privileges to everything to the user group but it failed at the point where you are supposed to import the application into a newly created database – telling me, not unreasonably, that it was secured by a workgroup file that I hadn’t provided.
The breakthrough came when I was reading about the security provided by Access 2007, and in particular the new accdb file format. User level security no longer applies to these file formats and Access removes any security it has applied when you convert to this database format.
So the process turns out to be simple. Just follow the following steps (using Access 2010 – I assume earlier versions will have equivalent options):-
- Start Access up as normal for a secured file (ie specify the workgroup file on the command line)
- Select the file tab and chose the “Save and Publish” option. Select the file format for the accdb format and click on the large “Save As” button
- When the conversion completes (mine took about 5 minutes – it is not an immediate process) and the file is saved in the new format close down Access
- Restart access without a work group file and create a new blank database in the the earlier Access file format (I am using Access 2000 format)
- Use the “External Data” tab and select an “Access” filetype to import from
- Import all objects from the file that you saved in the accdb format
The only issues I have had with that process so far are
- Some of the reports were set up for a printed not on my current development machine. I will have to re do the printer setup for those
- I was missing the references to ADO and DAO objects, so I had to manually re-add them (from Visual Basic, Tools, References menu).