Access to SQL Server Part 2: Linking

Introduction

This is part 2 of a three-part series discussing ways to use MS Access as a front end to SQL Server. In the first article, I demonstrated how to use the Upsizing Wizard to create a new SQL Server database from an Access database. In this article I describe how to link an Access database to an existing SQL Server database.

Designing a database, creating tables, forms, queries, and reports in Access are beyond the scope of these articles. I'm going to assume that you know that clicking "Next" moves you to the next step in a wizard and know to click "OK" to accept changes. I also will assume that you have the necessary privileges and permissions in SQL Server.

Create an ODBC data source (DSN)

For this example, a file DSN pointing to an existing SQL database should be created. I'm going to explain the steps here; click to skip this section if you are already familiar with creating a file DSN.

Open the ODBC Data Source Administrator and click on the File DSN tab. This can be found either in Control Panel or Administrative Tools depending on which client OS you are running. The name of the applet may be slightly different as well. I prefer using the file type DSN for Access applications. With this type of DSN, the connection string becomes part of the table definition so you don't have to set the data source up on the customer's computers.

After opening the ODBC Data Source Administrator, follow these steps to create the DSN:

• Click Add….
• The Create New Data Source Wizard pops up. Scroll down, select SQL Server.
• Enter a name for the file. Type in something meaningful, this will also be the name of the DSN. Click Next and Finish.
• Here it becomes a little confusing as another wizard starts up. Fill in a description if you wish, and select the server.
• Select the type of authentication to use. You can also click Advanced for options relating to the network protocol.
• Set the Default Database to the database you wish to link to.
• Set regional options if necessary and click Finish.
• Here you can test the data source. Actually, if you've gotten this far, your connection is probably fine.

View the ODBC DSN Wizard steps

Linking to an existing SQL database

For this example, I am going to start with a new blank Access database and link to the tables in the database created in the first article. This is much easier if you first set up a file DSN pointing to the SQL database. After you open your new blank database, follow these steps:

• In Access, go to File -> Get External Data -> Link Tables…. Note: If you accidentally choose, Import, you will copy the data, but you will not maintain a link to SQL.
• A File Open dialog pops up. Choose ODBC Databases().
• Instantly the ODBC DSN dialog appears and allows you to choose the DSN.
• A list of user tables appears. Select the tables you are interested in linking.

  If you have used SQL Server security to set up your DSN, you can save the password in the connection string by checking Save Password. Otherwise, your customer will be prompted for a login and password each time the database is opened. You can avoid this problem by using Windows authentication.

  Finally you click OK and the table links are created.

• In order to modify the data in a linked table the primary key must be defined. While the table links are being created, you will be prompted to provide a Unique Identifier for tables with no primary key. I strongly suggest that you define the primary keys in the SQL tables before attempting to link.

View the steps to link an existing database

Figure 1. Linked tables

Now you can view the linked tables in Access. Note that the table names also include the owner or schema name.

You can easily rename each table, if you choose, by right-clicking and selecting Rename. Here is a subroutine you can add to a VBA module that will do the work for you if you have many tables to rename:

Sub RemoveDBO()
    Dim tbl As TableDef
    For Each tbl In CurrentDb.TableDefs
        If Len(tbl.Connect) > 0 Then
            tbl.Name = Replace(tbl.Name, "dbo_", "")
        End If
    Next
    Set tbl = Nothing    
End Sub

All routines in this article require that the database references the Microsoft DAO Object Library. You may have to set up the reference to the DAO library. Open a Module and click Tools--> References . Find the Microsoft DAO Object Library in the list, check the box, and click OK.

Figure 2. How to set a reference to the DAO Library

Now you have updatable tables linked to SQL. You can almost treat these as regular Access tables in queries, forms, and reports. I suggest that you use the great tools and wizards that Access provides to create a good user interface and discourage access directly to the linked tables by your customers.

Re-linking tables

Now that you have created your SQL/Access application with tables linked to your development server or the MSDE instance running on your workstation, you will probably want to repoint the tables to another server. Access provides a tool to make this easy, the Linked Table Manager.

Begin by creating a file DSN pointing to your new server. Go to Tools--> Database Utilities--> Linked Table Manager…. You will probably want to select all of the tables and check Always Prompt for New Location. By checking this option, you will be prompted to provide new connection information. Otherwise, you will just refresh the existing connections.

Figure 3. The Linked Table Manager

You can also relink tables using code. Here is an example subroutine to modify the server parameter:

Sub LinkToNewServer(ByVal newServer As String, ByVal oldServer As String)
    Dim tbl As TableDef
    For Each tbl In CurrentDb.TableDefs
        If Len(tbl.Connect) > 0 Then
            tbl.Connect = Replace(tbl.Connect, oldServer, newServer)
            tbl.RefreshLink
        End If
    Next
    Set tbl = Nothing
End Sub

If the table definitions are modified in the SQL database, you must refresh the links in order for Access to recognize the changes.

Conclusions

MS Access provides a rich set of tools for quickly developing database applications. The data can be inside the Access application or links can be set up to other data sources. This article discussed how to link an Access database application to an existing SQL Server database. The third and final article in the series will cover how to get started with Access Projects.