As your business grew, it’s very possible you also grew an in-house Microsoft Access application that you still use to run some aspect of your business. Maybe an employee created it to solve an immediate problem and your team began to rely on it over time. And now you’re starting to have problems with it. What do you do?
Backup your Access database files. Often!
The first thing we tell customers is this: Backup your Access files! Back them up daily or even more often if the data changes often. Backup all database files, including the front end files and data files, if the application is already properly separated.
Few things are worse than losing important data. Even regular end users can develop apps quickly with Access. However, you might lose data if it isn’t done correctly. Backup your files often! Protect yourself against data loss.
Your files are backed up! Now what?
Who is using your Access database and how are they using it? Does only one employee use the file? Or only one employee at a time? Or do you have many employees using this Access database all day long, every day? If you’re in a single-user environment, you may be okay as long as you have good backups.
How is your environment set up? Do you have a single Microsoft Access database file stored on a network share that everyone uses? If so, you are going to have a file corruption issue. If your Access file gets corrupted, your only option is to pull the file from your backup. And nothing corrupts an Access file quicker than multiple users opening the same file at the same time.
What is the best way to set up a Microsoft Access application in a multi-user environment?
We prefer to develop multi-user systems using a robust relational database system. But since that isn’t always possible, there are ways you can set up your easy-to-develop Microsoft Access database so that it won’t crash and burn.
First and foremost, separate the front end from the back end! A proper multi-user Access database will have a file that contains the forms and code and a second file that contains the data only. You would connect the application file to the data file using either ODBC or the linked table manager.
To be safe, each user could have their own copy of the front end database file. The data file itself should be in a central location, accessible by all the clients. Users could still access the raw data if needed using linked tables, but the separation keeps the data much safer.
Writing good code can also have a big effect on how stable, scalable, and safe the application is. VB Script makes it easy to write bad code, but it also makes it easy to write good code.
What can Ravine Software do to help with your Microsoft Access application?
If your Microsoft Access database is still in one file, we can help by separating it out into two files. We can help you establish an appropriate backup plan. In addition, we can also add security to your application by setting up usernames and passwords and roles for your end users.
We can also do a full code audit. If you are having performance problems, there is a lot we can do in the code to improve the efficiency of your application.
We are also here to help by adding features and functionality to your application so you can get more use out of it.
Your team has grown beyond Microsoft Access. What options do you have?
Sometimes our customers have found that continuing to rely on a Microsoft Access application just isn’t the best way forward. When you have too many users or too many file corruptions or if performance is a big concern, it may be time to look at other options.
Option 1: Upsize the data file to SQL Server
One important intermediate step you can take is to upsize your data file to SQL Server. Fortunately for small businesses, Microsoft offers SQL Server Express, which is the free version. It is often times enough for most Access database customers.
SQL Server is a MUCH more robust multi-user relational database. SQL Server handles many simultaneous users. Your app will run much faster and your data will be much safer when it’s stored in SQL Server.
Upsizing to SQL Server is actually not that bad of a project. There is even an upsizing wizard in Access. After your data is moved to SQL Server, the linked tables in the Microsoft Access application file would be updated to point to the new location, and you’re done!
Well, not always. There are sometimes differences between Access and SQL Server that need to be addressed, but they’re usually easy to handle for most applications. And sometimes it is best to rewrite queries to take advantage of the new system.
Once the upsizing is complete, you can usually get a lot more life out of your app before you have to proceed to Option 2.
Option 2: A full rewrite of your Microsoft Access application
Even after upsizing your data file to SQL Server, there are many limitations to Microsoft Access applications in large multi-user environments.
Deployment and accessibility are two big concerns with an Access application. You either have to maintain separate copies of the front end on everyone’s computer which would need to be updated with each rollout, or you have to install a shortcut to each end user’s desktop. Additionally, in either case, the user would have to be on your internal network in order to use the app.
If your workforce is mobile, and especially in today’s chaotic work environment, it is very important for your applications to be flexible. Not everyone can remote in to your network every time they want to work.
Rewriting your app may be the best solution. We encourage our customers to take advantage of the web and convert their client/server apps to web applications. This solves many issues, but especially those related to performance, scalability, accessibility, and availability.
If we start with an existing application, the migration is easier than you think. We would already have a starting point to work from that includes individual screens and much program and business logic, which could be copied or otherwise duplicated.
With your app migrated to the web, it could be available to any of your employees from anywhere in the world, at any time. Updates are immediately rolled out to all users at the same time. You have tight control over versioning and security. You are also able to monitor performance and usage and more quickly respond to any issues that may occur.
Let us help with your Microsoft Access application today!
Feel free to contact us with any questions! We’d be happy to offer a consultation and then recommend the best path for you and your Microsoft Access application.
Our goal is to keep your team productive and your data safe. Contact us today to get started!
