Express Essentials: Connecting Access ADP Projects to SQL Server Express

In a recent column, "Migrating from Access 2007 to SQL Server Express," I showed how to use linked tables to connect Microsoft Access databases to SQL Server. That approach uses an ODBC connection to link Access tables to tables that are on the SQL Server system. But an even better way to connect Access to SQL Server Express is to use an Access database project. With Access database projects, you essentially use Access to develop a SQL Server database, giving you the best of both worlds.

Access database projects are a great answer to multiuser database applications. You can couple the productive, easy-to-use Access form, query, and report builders to the more powerful SQL Server multiuser relational database engine. When you use linked tables to connect to a SQL Server database, you can't change the table schema of the SQL Server database objects. But with an Access database project, you can open, create, modify, and delete SQL Server Express database objects such as tables, views, and stored procedures. In essence, Access database projects let you use Access as a database development and management front end to SQL Server Express.

However, Microsoft has made database projects quite a bit harder to find in Access 2007 than they were in earlier versions. In fact, those who don't know about Access 2007's support for database projects might never discover it.

To build an Access database project, open Access 2007, click the Office button, and select New. Click the folder browse icon next to the File Name prompt to display the New Database dialog box. Navigate to the directory in which you want save your project, select "Microsoft Office Access Projects (*.adp)" from the "Save as type" drop-down menu, then name the database at the File Name prompt. Click OK to return to the main Access window.

Next, click Create. Access displays the "Do you want to connect to an existing SQL Server database?" prompt. If you're starting from scratch, select No to create a new database. To connect to an existing database, select Yes.

For example purposes, let's connect to the sample Northwind database. Clicking Yes displays the Data Link Properties dialog box. If you installed SQL Server Express using the default values, select SQLEXPRESS from the "Select or enter a server name" drop-down menu and click "Use Windows NT Integrated security." From the "Select the database on the server" drop-down menu, choose the Northwind database. Clicking OK creates the Access database project and opens Access with the Northwind tables displayed. The layout is similar but not identical to a native Access database project. Clicking a table opens it, and you can change the table's schema as well as create new Access forms and reports.

Now you know how to create Access database projects. But what if you've already created an Access database using linked tables and you want to move it to an Access database project? I'll explain how to do that in the next edition of Express Essentials.