In my latest Express Essentials columns I've been covering ways to link Microsoft Access with SQL Server Express. Although it might seem a bit redundant to try to connect two different database products, doing so can provide several important benefits. SQL Server Express gives you a robust and scalable database engine while Access can provide an easy-to-use, productive database development environment.
In "Connecting Access ADP Projects to SQL Server Express", I covered using Access database projects to let Access act as a front end to SQL Server. That article explained how to create new projects. But what if you've already built a multiuser Access database and you've looking to move up to SQL Server Express to handle more concurrent users and improve performance? In this edition of Express Essentials I show how you can migrate from a multiuser Access database to an Access database project that uses SQL Server Express as the back end.
If your Access project was a serious multiuser database, it was probably split into a front-end database that contains the forms, queries, and reports and a back-end database that contains the data tables. Splitting the Access database lets the front-end portion of the database run locally on each user's PC. Multiple networked systems all access the shared back-end database. Tables in the front-end database are linked Access tables. When you have a split Access database, there are two tacks you can take to upgrade it to a SQL Server Express back end.
One approach is to run Access's Upsizing Wizard on the back-end database, which converts the Access tables to SQL Server tables. Then you can manually update all the linked tables at the front end to point to the new tables in the SQL Server database. However, if you take this approach you won't end up with an Access database project, which means you can't use Access to manage the SQL Server database and you can't take advantage of native SQL Server database objects such as stored procedures.
A much better approach is to run the Upsizing Wizard on the front-end database. The wizard can handle converting the linked tables to SQL Server tables and can also retrieve the data from the back-end database. I've covered using the Upsizing Wizard in the past, but converting to an Access database project is a little different than converting to linked tables.
First, open your front-end database using Access 2007 and start the wizard from the Ribbon's Database Tools tab. Select SQL Server from the Move Data section, then select "Create new database" and click Next. On the next dialog box enter the SQLEXPRESS instance name (the default is SQLEXPRESS), the authentication information, and the name of the database that will be created on SQL Server Express. Click Next. Select the tables to migrate--clicking the double arrow selects all the tables--and click Next. Accept all the default values, which include upsizing Indexes, Validation rules, Defaults, and Table relationships, and click Next. To create an Access database project, select the "Create a new Access client/server application" check box and name the project file. By default, the Upsizing Wizard appends CS to the end of the name of your Access database and uses that name with a file extension of .adp. Click Next, then Finish.
At the end of the process, Access opens your new project and your Access application is connected to the more powerful and robust SQL Server Express database engine. You can then deploy the new Access database project to the systems that were using the old Access front-end database.
