With a stand-alone Microsoft Access Database design approach, you have complete control over managing the data and their objects but if you are going to want to share your Access database; simply loading your .MDB or .ACCDB file on a shared network folder will cause problems further down the line!
So sharing an Access database is a methodology often over looked and needs to be planned at the earliest opportunity leveraging your time and effort in order to avoid performance and functional issues later on.
There are several ways to build and implement a Microsoft Access database share environment.
Let’s first introduce the methods available followed by identifying the tools required and their pros and cons.
Microsoft Access Database: Network Folder
You can share a database in a network folder which is the simplest and easiest way to allow multiple users to gain access to your database file offering the basic functionality of a normal stand-alone environment.
However, performance, reliability and other issues will be more frequent as everyone will hit (concurrent) the same objects namely Tables, Queries, Forms, Reports and any Macro/VBA code procedures.
All data (from Tables) is also stored in one database container with all the other objects which is a security issue – more so for data loss.
Probably the most problematic area is when two or more users attempt to read and write to the same record from the same table causing a data lock and freezing. Even though there are properties to reduce locking, it’s simply not strong enough to handle multiple concurrent hits.
Microsoft Access Database: Split Database
This is probably the most popular method and by the very nature of this method you split and divide your Access database into two separate database files.
The first is where all your Tables will be stored in one database file known as the ‘Back-End’ database and the remaining objects sit in the second database file known as the ‘Front-End’ database which points to the ‘Back-End’ file as table links.
The ‘Back-End’ database is stored on a shared network folder and each ‘Front-End’ database is locally stored for each user who then have their own version and controlling their own objects.
The advantage here is performance is much better as users call only the data (from tables) when they load or save records using the client side objects to process any other action.
It also means each user can customise objects; build individual queries and reports without relying on a central database file being interrupted and be self sufficient!
The best part of this methodology is that the data is protected (elsewhere on the shared folder) and if an individual user accidentally deletes their own ‘Front-End’ database then at least your data is preserved leaving the only task to reload another ‘local’ client database file.
Microsoft Access Database: SharePoint Services
New to Access (version 2010) is the ability to publish your database to the web and make it an online version taking advantage of the ‘hybrid‘ database features too.
There are several ways within this type of service but in essence using a (web) server like SharePoint opens up a very convenient way to share information even for users who do not have Microsoft Access installed meaning they can use Access via a web browser and maintain data.
There are some compliance issues when uploading a hybrid Access database to a web database and some further research is required to know your options and can be found in Access help.
One downside for small companies and individuals in particular who do not have a SharePoint web server is that you will need to subscribe to a web host provider who can offer this service (usually for a small fee).
Microsoft Access Database: SQL Server Database
This option is similar to splitting your Microsoft Access database with the added beneift of higher end functionality including performance, robustness, security and more server side processing.
The tool in Access uses the ‘Upsizing Wizard’ and splits your database creating a SQL server engine environment of your tables leaving all the remaining objects managed from the client as the ‘Front-End’.
This is useful especailly for companies or groups of users who may have different versions of Microsoft Access in use where a central data storage for your tables will be able to work across different versions.
Users can then have user accounts created on the server side adding extra security and protect sensitive areas of data.
Other advantages includes the handling of record locking as described with network folder sharing and better back-up and recovery processes are managed on the server side too.
Don’t have the Microsoft Access database application?
No problem, you can use the free Access runtime (download from my Free Access Stuff page) but you will not be able to edit and design the objects. Someone with a full version can deploy a copy and even create an executable version (.MDE and .ACCDE) which helps with performance too and you will be able to run the database file and maintain the records and run reports.
Web users also do not need to worry about having a full licenced version either as you access your database file via the web (as discussed in SharePoint Services).
Take a look at my eBooks to know more about the various objects and design methodologies of Microsoft Access – there’s a money back guarantee and a FREE 30 day email support.
So, there you have it! PLAN, DESIGN and IMPLEMENT your Microsoft Access database.