Split a database into front and back ends

- If you're database will be used by several people at once, you should consider splitting it. Splitting a database converts it from one file, into two linked files called a back end and a front end. The back end file will contain all of your data tables and can be stored on a network server so that everyone has access to the same information. Access provides a tool that easily split your database into the two components and you can find it on the database tools tab of the ribbon in the move data group. Here's the button that we're looking for, it just says access database. Now even though the name of the button isn't very descriptive, the icon gives you an idea about what's about to happen.

One database will become two parts. Let's go ahead and click on it and the database splitter wizard starts. It gives you a description about what splitting a database means. When you press the split database button, access will move all of your data tables into a new back end file which can be moved to a commonly shared location on your company's network. The remaining interface objects such as queries, forms, and reports will become something that's called the front end. Each person can keep a copy of the front end file on their local computer. This gives everyone the best of both worlds. When interacting with the database, running a query, or printing a report, for instance, the database runs quickly because it doesn't have to transfer each mouse click over the network.

But you still get the benefit of having everyone share the same data tables. So for example, if someone in a different department adds a new vendor, everyone accessing the database will instantly be able to make use of that new vendor in their product orders. Let's go ahead and press the split database button, and that will bring up the create back end database window. This is where we're going to save our back end file. I'll browse up to the desktop and place it inside the chapter eight folder of the exercise files. Notice that it automatically appended an underscore b-e to the end of our file name. This is a common convention that reminds you that this is the back end file.

Go ahead and press the split button to save the database. When it's done, we get this message that it was successfully split, go ahead and press OK. And then we can go ahead and close down the database and take a look at what happened on the file explorer. Here is our original file, the Landon Hotel split access database. This is now our front end, and here is our back end where all of our data tables are. So what happens if this file gets moved to a new location? I'm going to move this b-e file into the network folder that I have up here, it's just an empty folder. This will simulate moving it to a new location on my company network.

Then, I'll go ahead and open up the Landon Hotel split file one more time. Notice that all the icons from my tables have now changed to a blue arrow icon. This indicates that they are now linked tables to that back end file. In the tool tip that appears below my mouse though, it's telling me that it's expecting to find that b-e file in the chapter eight folder, not in the network file. If I try and open up one of these tables, I'm gonna get an error message saying that it can't find that table. Let's go ahead and say OK to that. To fix this and update the links, we're gonna go back to the external data tab.

Then, we're gonna open up the linked table manager. The linked table manager will show me a listing of all the linked tables in my database. I'll simply press the select all button to place a check mark next to all of those and then press the OK button. That'll go through and re link all of my files to the new back end. Let's go ahead and browse up to my computer and find it. Again, it's on my desktop, exercise files, chapter eight. Get into the network folder and there it is. We'll select it and press open. I get the message that all selected linked tables were successfully refreshed so that's a good sign.

Press OK. We'll close out of the linked table manager, and now I should be able to open up my tables and see the data once again. So splitting your database provides an easy way to give each user their own customized front end. With forms, queries, and reports specific to their tasks or areas of responsibility. By storing all of the data from those tasks into a commonly accessed back end file stored somewhere on the network, everyone in the organization will be pulling from and writing to the same pool of shared data so that everyone is up to date.