Linking to Data in SQL Server Tables

You may need to connect to SQL Server databases for a company, university, or other organization that stores its data in SQL Server. If you need to link an Access database to data in existing SQL Server tables, or create a new Access front end for SQL Server tables, the process is different.

sample databases you can download from the SQL Server 2005 Samples and Sample

Databases (February 2007) page on the Microsoft web site, at this link: www.microsoft.com/ downloads/details.aspx?FamilyID=e719ecf7-9f46-4312-af89-6ad8702e4e6e& DisplayLang=en#filelist (or possibly a page with a later date). Make sure that any sample databases you download are compatible with Access 2007. Those on the December 2006 page are compatible with Access 2007; other sample databases posted earlier are not.

Download the AdventureWorksDB.msi file, and install it by double-clicking it. This will create the SQL Server database AdventureWorks_Data.mdf in your SQL Server data folder, usually C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data.

Next, create a new, blank Access 2007 database by selecting New from the File menu. On the dialog that opens next (Figure 18.48), select the Blank Database selection, and enter the name for the new front-end database.

Close the default table (Table1) that is automatically created.

To link to the SQL Server database, follow these steps:

1. Drop down the More list on the Import group on the External Data tab, and select the ODBC Database item, as shown in Figure 18.49.

The SQL Server database I connect to in this section is AdventureWorks, one of the

Creating the new blank database.

Creating the new blank database.

FIGURE 18.49

Starting the process of linking to a SQL Server database.

FIGURE 18.49

Starting the process of linking to a SQL Server database.

2. On the Get External Data screen (Figure 18.50), select the Link option.

Was this article helpful?

0 0

Post a comment