Express Essentials: Use SQL Server Express as a Back End to Access

Over the past couple of columns I've covered integrating SQL Server Express with various Microsoft Office 2007 products. In "Express Essentials: Excel Integration," InstantDoc ID 96444, I covered pulling SQL Server Express data into Excel 2007, and in "Word 2007 Integration," InstantDoc ID 96465, I showed how you can do Word 2007 mail merges using database data found in SQL Server Express. This week, I wrap the Office integration series by explaining how to use SQL Server Express as a back end to Microsoft Office Access.

Access supports a couple of methods of connecting to a SQL Server Express database. You can migrate Access to SQL Server Express using the Upsizing Wizard, as I covered in "Express Essentials: Migrating from Access 2007 to SQL Server Express," InstantDoc ID 96299. But you can also use an Access feature called linked tables to create an ODBC link between Access tables and SQL Server Express tables. When you're using linked tables, it's important to remember that linked tables actually reside on the SQL Server Express database and not in Access--Access just creates a link to them.

If you want to use Access as a development front end to your SQL Server Express database, start by opening Access, selecting Blank Database, giving the database a name, and clicking Create. Then click the Get External Data tab on the Ribbon and select More, then ODBC Database to display the Get External Data - ODBC Database dialog box. Here you have the choice of either importing the tables, which essentially creates a point-in-time snapshot of the data that will reside in Access, or linking to the tables. Linking to the tables creates an updateable connection; the tables themselves continue to reside on the SQL Server Express system.

To create linked tables, choose the "Link to the data source by creating a linked table" option, then click Next. If this is the first time you've used ODBC from Access 2007, you'll need to click the New button to create a new ODBC data source. From the Create New Data Source dialog box, select SQL Native Client, then click Advanced. In the driver specific keywords box, add the line

database=Northwind

to link to the sample Northwind database. Click Next, click Name the Data Source, then give the data source a name--I'll use SQLEXPRESS. Click OK, then Finish.

Type SQLEXPRESS in the Server drop-down list, click Finish again, then click OK in the ODBC Microsoft SQL Server Setup dialog box. You're returned to the Select Data Source dialog box, in which the new SQLEXPRESS.dsn data source has been selected. Clicking OK displays the tables in the SQL Server Northwind database.

You can select one or more tables, but for this example, just select dbo.Customers and click OK. The table dbo.Customers (prefixed with a globe icon to indicate that it's a linked table) is added to your Access database. Double-clicking dbo.Customers sends a query to SQL Server Express and returns all the table's rows into Access.

Creating Access 2007 linked tables to SQL Server Express is easy, but there are a couple of caveats about using them, which I discuss in the next issue of SQL Server Express UPDATE.