In recent blogs, I've been discussing the various ways you can connect data between Microsoft products. So far, I've covered Access, Excel and SQL Server. Today, I'll be discussing linking one Microsoft Access database to another.
Let's start with the most obvious question: WHY?
Why would you want to link one Access database to another? There are several good reasons for this, including:
Connecting multiple existing database. Maybe you've already got one database the contains your sales data, and another one that manages inventory data. There's going to be some overlap between those type data sets, so why not link them together?
Security concerns. Perhaps you've got one database that many people in your organization are allowed to look at, such as your weekly shift schedule, but you've got related data that is sensitive, like payroll data. While most employees only need access to the scheduling database, your HR manager needs access to both. In that case, the payroll database can be kept in a secure folder that only the HR manager can access. It can be linked to the scheduling database, in order to pull in relevant information.
A split database design. In this scenario, you have one Access database that contains the front-end objects, like forms and reports; and you have a second Access database that houses the tables. It's a common scenario when you've got a database that will be shared by multiple users. Let's dig deeper into this option.
Split Database Design
The split database design is a common choice in the world of Microsoft Access. It looks like this:
As you can see, there are two Access database: a front-end and a back-end.
The back-end database, which resides on your server, contains only tables.
The front-end database is installed on each user's computer, and contains all other objects, including forms, reports, queries and macros.
Obviously, the two are linked together.
Benefit
Storing your Access database on the server allows you to easily perform backups and keep the file secure. Of course, you can do both of those things on individual laptop or desktop computer, but its easier when its in a central location.
So why not just keep all of your objects in a single database on the server?
The split database design significantly improves performance. Microsoft Access is a great tool, but one of its shortcomings is that it doesn't perform well when used over a network. With only a few hundred records in your database, users may start to complain that it takes too long to open a form or run a report.
By splitting the database, you cause Access to do most of its heavy lifting on the local computer. Although the data still needs to be shuttled back-and-forth, the burden of presentation stays local. This makes for a noticeably faster user interface.
Corruption
The split database design also reduces the chance of database corruption, an ever-present threat with Access. As I mentioned previously, Access doesn't always play nicely when used over a network. If your database resides on your server, sooner or later you'll probably experience a corruption. But with the split database design, you've see it less often.
FYI, database corruption in Access is usually quick and easy to fix, but always make sure your back-end database (the one with the tables) is backed up regularly.
Enhancements
One more reason to love a split database design is that it makes it easy to roll out enhancements. As a developer, I would have a copy of both your front-end and your back-end databases. I'd make my changes to the front-end, using my copy of the back-end for testing. I can add, change and delete records in my copy in order to facilitate my testing.
When I'm done, I'll simply roll-out the new front-end to all users, linking it to their back-end tables. This way, my test data won't impact the live data. By keeping the two files separate, it makes it easy to implement one without impacting the other.
Splitting your Database
The process of splitting your database is simple. The steps are:
Create an empty Access database
Import all of your tables into the new database. Do not link - be sure to import
Open your original database, and delete all tables
Link to the tables in the new back-end that you created in step 2
That's all there is to it. And, as if that wasn't easy enough, there's an option on the "Database Tools" menu that'll walk you through it:
When you click "Access Database" on the "Database Tools" menu, it'll launch the "Database Splitter" tool. Walk through the steps as directed by the tool, and you'll have a split database on your hands.
Conclusion
As I'm sure you can see, the split database design is the way to go for multi-user Access databases. Your data will be safer and more secure, and your users will be happier with the quick response times that this design offers.
In my next Access blog, I'll dive deeper into the database corruption issues that crop up with Microsoft Access. We'll look at some common symptoms, as well as some that are harder to recognize. We'll see how to fix them and how to prevent them. To be notified when that post goes live, subscribe using the form below.
Got questions about the Access split database design? Let's talk through it to see if it makes sense in your case. Reach out to me via email or phone to set up a time to talk.
Kim
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness
© 2023 North Port Solutions, LLC. All right reserved.