Join Adam Wilbert for an in-depth discussion in this video Understanding front ends and back ends, part of Migrating Access Databases to SQL Server.
- View Offline
- One of the easiest ways to increase the performance and reliability of an Access database is to chop it into one or more databases. This is a processing called splitting, where the data tables get placed into one database file, and all of the other objects, including queries, forms, and reports, get placed in another. Let's take a look at how this currently works inside of Access. Right now I've go the Kinetico file open from our Chapter 1 folder. I'm going to come up to the Database Tools tab, and in the Move Data section, I'm going to click on this button here, that says Access Database. That'll start up the Database Splitting Wizard.
I can read through the text here about what's about to happen, and then press Split Database. That'll open up a file exploring window where I can browse out to my desktop, and go into the Exercise Files folder here, and into Chapter 1. Now I'm going to save the back end file. You can see that the designated file name here has the "_be" added to the name. This indicates that it's going to be the back end file to the regular database, which will still be the front end. Let's go ahead and press the Split button here. After a moment, I get a window here that tells me that the Access database was successfully split, I'll press OK, and I can return back into the front end of the database.
Now, the only change I can see that's been made here, is that over in my navigation pane here, these tables are all now linked tables. They're no longer local to this front end of the file. I can double-click to open them, and they open up just like normal. Let's go ahead and close this down, and take a look at our Exercise Files folder. Here I can see I have two files now. I've got the front end file here, which has all of my forum and report objects, and then I have the other file, the back end here. I can go ahead and double-click on that to open it up. And you can see that these tables are local to this particular file.
Again, if I double-click on them, I can see all the data inside. Now we have two separate files that combine to make up the database. We can place the back end on a network server, and lots of people can connect to it in order to share the same pool of data. Plus, each individual user can use their own copy of the front end file on their local machine. That makes the database more responsive to user input, and allows each user to customize their queries and reports while still sharing the same underlying data with everyone else. In the front end, the original tables are now linked to their new location on the disk.
The downside of this arrangement is that both the front end and the back end files are still just Access databases. They use a file-based approach to storing data on disk, which is still subject to the restrictions of the Access database engine in terms of file size, and they provide only limited protections against data loss or corruption. A better and more robust solution is to take advantage of SQL Server to store the back end data. SQL Server is a database management system, and in many ways is similar to the management system that's built into Access, which is called JET.
The main purpose of a database engine, is to simply store and retrieve records. And SQL Server excels at it. SQL Server provides many enhancements to the process of storing data, including increased storage capactiy, the ability to better utilize your computer's hardware to increase performance, and there are much stronger protections in place to keep the data secure. What SQL Server lacks, though, is the ability to create the kinds of user interface elements and program controls that we're used to using inside of Access through the use of form and report objects.
In very large systems, a custom front end application might be created. In our case, we'll continue to use Access to provide these types of supporting objects and interfaces. In both cases though, from large to small, SQL Server acts as the repository, which is connected through various clients. Throughout this course, we're going to essentially do the same thing as we just did within Access. We're going to split off parts of our database, but instead of using just another file-based solution like we did here, we're going to see how we can begin looping in SQL Server into the mix.
Watch this course to learn how to leverage what you already know to take that next step. Adam Wilbert reviews the basics of SQL Server Management Studio (SSMS) and introduces two workflows for migrating Access data: one using a simple export/import process and the other using the free SQL Server Migration Assistant (SSMA). He also shows how to convert Access to SQL data types, link Access to SQL data via an ODBC connection, and address special concerns for Access 2010 data.
- Why upgrade?
- Importing a SQL database
- Exploring data types and server objects
- Installing SSMA
- Mapping source and target data types
- Using the Migration Wizard
- Linking to migrated tables in SSMA
- Working with views and stored procedures
- Linking Access to SQL Server databases