Linking SQL Express 2005 Tables to MS Access 2007

This tutorial shows you how to link tables on your SQL 2005 Express server to an MS Access 2007 database. Some of the salient features of the MS Access 2007 program were discussed in an earlier tutorial including a detailed description of the "Ribbon." Reading the earlier article will help you to go through this tutorial with ease.

Introduction

With Access 2007 you can create read/write linked tables to SQL Server Tables/Views. Linking tables is one of the ways to get access to external data and create front ends without actually having the data locally. If you want to hold on to the table structure and give permission to create front-end applications to others, this is the preferred method of data access. The other methods are copying and importing data. When you link tables which are in an external source you will not be permitted to make changes to the structure of the tables, nor to make design changes to the tables. You may make such changes at the source.

Steps for successful linking

The tables to be linked are on a SQL 2005 Express server. This could be on a connected or a networked machine. In this tutorial it is on the local machine.

A new install of SQL 2005 Express server has only the system databases. Hence a Northwind database was brought into the SQL 2005 Express server using the ExportImport task. The ODBC connection to be described will be made to this database, called Windy. This database has all the tables from the Northwind database.

{mospagebreak title=Creating an ODBC Data Source}

On a machine with Windows OS it is easy to create an ODBC Data Source — a go between for your application and the data on your server. From Start–>Control Panel on your machine you get access to the Administrative Tools icon. Double clicking this icon will open up a new window where you can see the Data Sources (ODBC) icon. Double clicking the icon should bring up the next window shown below. This is your gateway to get connected with any of the database servers/programs that provide an ODBC connection option.

Here a System DSN will be created which all the users on this machine can use to access the data. Click on the Add… button to open the Create New Data Source wizard’s window as shown. Here a SQL Server driver was chosen although a SQL Native Client would also do well.

When you click on the Finish button you will get to the next step of creating a Data Source for a SQL Server.

You will give a name to the data source, which is called AccWindy here. The description is just a note that Windy is really the Northwind database. The drop-down that follows should reveal all the available servers both local and networked. Here the Hodentek2SQL Express server is chosen; Hodentek2 is the machine name.

Clicking on the Next button in the above window opens the window where you are required to fill in the authentication information. Here a SQL Server authentication is used for which a username and password are required.

Clicking on the Next button in the above window takes you to a step where you can set the default database, which is usually the master database. You can use the drop-down to choose the database. You can also set the ANSI related parameters. Here the defaults are accepted.

Clicking on the Next button then guides you through several other options related to the language for system messages; encryption; character data translation; and so forth. Defaults are accepted for this tutorial.

This bring us to the final window of the ODBC data source creation step. When you click on the Finish button you will get to the screen where the information you provided and the choices you made in the configuration of the data source are summarized. You may also test the success or failure of this DSN by using the Test Data Source… button.

When you click OK after you verify it is successful, you will get back to the first screen of the ODBC Data Source Administrator displaying the source you created. This DSN can now be used in any application which asks you to furnish a connection information and can be a part of the ConnectionString.

{mospagebreak title=Creating an Access Database}

Click on the Microsoft Office Icon in the MS Access 2007 program to open the next window. Some of the features of this new version of MS Access were described in an earlier tutorial, one of the key feature being the ribbon. In this window you can open an existing database, create a new one or otherwise manage an existing database.

Click on New… to open the window to create a Blank Database. This should appear on the right hand bottom portion of the main window as shown. You give a file name and it will be saved to the default location, which continues to be My Documents.

When you click on the Create button you will create the blank database. Now click on the External Data tab on the ribbon, which reveals a drop-down as shown in the next picture. Choose the first option, namely ODBC Database. Make a note of the other options that you can use for future reference.

Clicking on the first option above opens the next window, Get External Data – ODBC Database. This is where you begin to link the tables.

{mospagebreak title=Linking Tables}

Choose the second option, Link to the data source by creating a linked table. Clicking on the OK button opens the next window, where you need to select the data source. Here the AccWindy data source will be used. You can also start creating a new data source from here by clicking the New… button, which will open up the ODBC Data Source Administrator window seen earlier. This window opens up with the File Data Source by default; you need to change to the Machine Data Source tab.

When you click on the OK button in the above window you will get the SQL Server Login window, where you need to fill in the login details used during the AccWindy data source creation step.

Fill in the information and then click on the OK button. This opens up the Link Tables window displaying all the available tables in the database.

You can select multiple tables using your mouse by holding down the Shift key. By clicking on the OK button in the above window you will get a number of windows popping up, one for each table you are using, so that you can Select a Unique Record Identifier. This is needed for modifying records in the database. The next picture is a composite of two screens; the top one is showing the process that is taking place and the bottom one is the unique record. Make sure you read the information at the bottom of the screen.

{mospagebreak title=Displaying Linked Tables}

The linked tables appear on the left hand side of the main window of the application as shown below. Only a portion of the main window is shown here. It shows six of the tables that are linked to the SQL Express server database. Right clicking the table reveals the drop-down from which you can choose an appropriate option.

In the All Tables tab (click on the down arrow head) you can choose a number of options such as going to a particular table (Table 1 is a default table created when you create a blank database) as well as look at created or modified date sorted views. It is left to the reader to experiment with the various options.

Single Table Display

When you place a cursor on a table you get the object (Table) properties as shown in the not so clear next picture. For this particular example it is displaying in the tooltip the following text:

ODBC; DSN=AccWindy; Description=Windy=Northwind;
App=Microsoft Office 2007; Database=Windy;
Language=us_english;Table=dbo.Customers

When you click on the Linked Table Manager you will get a window where you can choose the table links to be updated. This is useful if you are distributing your application, or if the sources have changed their location and old links are broken. The Linked Table Manager has evolved from the Attachment Manager in Access 2.0.

You will be able to access the data in the table by using the option Open in the drop-down shown in an earlier window. Here is a partial view of the data in the Customers table. The tabbed window allows you to open up more windows and switch easily between them.

Trying to view the design of the table pops up this warning message. It says that you cannot change the design of the linked table and what you may see is read-only. You can only make design changes in the source (in this case in SQL Express).

If you click "Yes" and continue you will see the design of the page as shown. It is very similar to earlier versions. You can see Smart Tags as one of the values you can attach to a column. Of course you may have to use an API kit to add this functionality. This has not been used or verified for this tutorial.

From the Database Tools tab you can access the Relationships window to which you can add tables or remove them. As shown here only tables have been added and they are not linked to one another.

Summary

Microsoft Access 2007 with all the novel changes to the interface makes it very easy to access all the necessary controls. Linking tables is as easy as it was in earlier versions without any changes. Office 2007 uses its own version of Jet whereas the previous versions used Jet provided with the Windows OS. Getting used to the new interface may require some effort. The reader may benefit further by reading the article on distributed queries using linked tables on a SQL server.

One thought on “Linking SQL Express 2005 Tables to MS Access 2007

  1. Linking is as easy as in earlier versions, if not easier. It is quite intuitive as well. After one gets used to the ‘tabbed’ pages and the ‘ribbon’ it gets a lot easier. Linking is especially suited for creating front ends with the data sitting where it is. Of course making changes to data is only by making changes to the source. Please read the other Access 2007 articles to get a more thorough understanding of the new interface.
    Happy reading.
    Looking forward to your comments and suggestions.
    Sincerely, jay

[gp-comments width="770" linklove="off" ]