ACCESS DATABASE DESIGN FOR REMOTE ACCESS
MS Access is not intended for storing data online...HOWEVER..
If you have arrived at this page and don't know much about the potential of MS Access then it might make sense to read this page first.
Recap: Access is excellent for:
- rapid application development
- small to medium size businesses
- individual projects in large organisations
- front end development using SQL Server back ends
- use by teams of people at the same time
MS Access is not designed for use over the Internet out of the box - it will eventually (and possibly quickly) result in you breaking the database if you try, however we can set things up such that you can use MS Access from multiple locations with Internet access, getting all of the usual advantages that come with Access without having to be in the office on the same network as your server and without the need for expensive licenses for multiple Remote Desktop users.
How?
MS Access is capable of being split into two parts (indeed this is almost always best practice)
- A back-end MS Access database file that holds the actual data
- A front-end MS Access file that holds the user interface and business logic
However a front-end Access file is capable of connecting to a variety of other database back ends and many of these are designed for remote connections and perform very well when paired with MS Access. We use this fact and combine MS Access front ends with SQL Server software over an encrypted connection (for data security).
We often use MySQL or MariaDB SQL servers for this purpose though also use MS SQL at times if that is preferred.
The Conversion Process
If you already have an Access database that you want converting for online use then the first stage will be for us to analyse the table structure of your system and check that it is properly "normalised". This is important because it ensures that the database doesn't have large quantities of repeated and redundant data. For a database to work well on the internet we have to reduce the amount of information being passed backwards and forwards as much as possible to maximise speed and usability.
Once the table structure is sorted out we create a SQL server version of the tables and migrate your data to it, connecting your MS Access front end to the back end SQL Server database.
Optimisation
Once the back end data is online there will be a working version of MS Access with data "on the cloud". Now it is time to test the database and see whether any of the business processes that involve larger quantities of data (and therefore slow things down) can be migrated to stored procedures and functions that can be run on the cloud.
Other Possibilities
Now your data is online it can be used to serve data on a website or can accept incoming data from a website designed to collect data. It could be used to create a service used by other web users or to power an app.
