Splitting your Access database offers numerous advantages, including increased flexibility, security, efficiency, and scalability.

Access is an amazing and powerful desktop application that lets you store data and interface with that data in the same file. You can even design your own applications with Access.  Unfortunately, the only way to utilize the full power of MS Access  is to develop traditional local desktop based applications.  Luckily, you can take your powerful MS Access applications with split frontend and backend to the cloud with Access Hosting’s Remote Desktop Hosting.

Most developers agree that a split database is easier to protect and maintain but unfortunately this powerful approach is completely incompatible with SharePoint.  When you split an Access database file, you end up with two files instead of just one (often the backend is an .mdb file):

• The backend stores all of the data in relational table (you can always move the backend to SQL for even more power and accessibility)
• The front end stores all of your interface objects: forms, reports, queries etc.
• By linking the two files, users can view and manipulate the data in the backend via the forms and reports in the front end. This arrangement solves a number of problems inherent to the Access file structure.

Now onto the top 10 reasons to split a Microsoft Access Database:

1: Multiple users share the data
Perhaps the biggest incentive for splitting a database is to supply data to multiple users over a local network or on the cloud via Access Hosting. By storing the backend on a local file server, SQL or other cloud based solution and distributing the front end to workstations and users, many users can access and manipulate the data at the same time without running into record locking and other problems.

2: Everyone’s using the same data at the same time
By splitting a database, you know that all users are accessing the most current data because everyone’s accessing the SAME data. Not only are they all accessing the same data, they can all update it at the same time. That means a change made by one user is almost immediately available to all other users.

Having a backend moves all the data into a single database file (.mdb) or backend ODBC connection (SQL, mySQL etc). That means there’s only one copy of that data to manage and protect. Changes are immediate and available to all authorized users. Any administrative and development duties are implemented in the backend file, once.

3: Your data is better protected
Whole books have been written on database security, but it’s enough for you to know that you must protect your data. One of the easiest ways is to split your database. Placing your tables in a backend file protects your database design because users can’t directly access the tables via the interface objects in the front end.  Therefore, they can’t alter or delete tables, even accidentally. Most of the users working in the front end won’t realize they’re actually working with two separate files, so splitting the database will have NO Negative impact on your users.

However, this arrangement is not a comprehensive security lock on design. Users who know what they’re doing can still open the backend, if they have access to it. Just bear in mind that splitting the database will minimize accidents — but it won’t stop someone who’s determined to get at your tables.

If security is a of utmost importance, Access Hosting’s Remote Desktop hosting adds an additional layer of security – requiring users to login to the MS Access frontend.  Users don’t even need to have Access installed on their computer!  We even offer HIPAA compliant dedicated server solutions for healthcare and medical data.  Moving your data to a SQL backend also adds additional security and features to an Access application.

4: You can easily scale your application for the future
If there’s any chance that your Access database will grow out of its skin, consider splitting the database. It’s easier to upsize a split database to SQL Server (or some other larger relational database system) because you can easily link the existing front end to SQL Server tables. That way, the organization has the advantage of storing data in a larger database with most of the perks that come with doing so, while still using the interface and all of the reports and queries that you originally designed in the Access front end.

5:  The user interface is easy to modify
Most databases grow and change with the business; they require new features or modified business rules. Changes to existing tables are rare, if you properly normalized them early on. Most changes will be in the front end in the form of new or modified forms and reports.

As long as your database is split, testing and implementing changes to the front end can occur with little or no disruption to users. You simply link the development front end to the production backend and test away. This won’t always be the case, of course, but testing new interface objects is easier in a split database configuration.

6: Deploying a new front end is a snap
If the user interface and data are stored in the same database, you must REPLACE the entire database EVERY time changes are made in a local environment.  With our RDP solution, you can keep your database and app contained all in one file and more easily replace them on our server in 1 location, but it’s still not ideal.  That’s a lot of unnecessary work and is especially problematic if no one in-house has the expertise to do it for you or if you have to visit each user’s workstation.

In a split database configuration, you simply replace the front-end ms access file and relink the tables. It takes a few minutes and requires little interruption of users.  With our RDP solution, you can even login and do this for every user to completely eliminate any application support issues.

7: It makes life easier for offsite developers
A split database is easier for offsite developers to maintain and upgrade. The developer works offsite to implement changes and enhancements to the front end and then ships the new version to someone in-house (or uploads it directly) who has the technical expertise to deploy it. This latter process is a simple copy and relinking task that doesn’t require high-end expertise. You can train someone to do it or even talk someone through it over the phone. Many developers write a routine that automates the process. All the in-house technician has to do is double-click the installation file. This opens up a lot of long distance opportunities that a developer just couldn’t manage as easily with a single database file.  Access Developers out there should interested in making their lives easier and moving their clients to a cloud based hosting solution should contact us about our Partner and Referral program.

8: Geography’s not a problem
A split database allows users in different locations to access the same data. For example, the backend could be stored on our servers in our SAS70 data center in Philadelphia, but users from all over the country can access the data via their local systems connected to a SQL backend.

9: Corruption is limited
Access databases are prone to corruption. One of the easiest ways to avoid this problem is to implement a split database, which is less prone to corruption.  Nothing is worse than having a user corrupt your backend data!

10: It’s easier to get individual users back on track
Security in the front end is one way to limit user interference. However, some users require more flexibility than others and there are always trade-offs. Some applications will require tight front-end security, while others will allow more freedom to tinker.

When a user tinkers to the point of destruction, a split database is easier to repair. Rather than bringing the entire application and all its users to a screeching halt, you have only one user who’s unable to work, momentarily because they broke their specific Access frontend and not the entire application. The fix is usually as simple as recopying the front end and overwriting the changes that the person made.

I hope this was helpful to people who have an Access application at their business.  If anyone is looking to learn more about our Remote Desktop and or SQL backend hosting options, please do not hesitate to contact us or sign up for a free trial to try it for yourself.