Programmatically changing SQL Server linked tables in Microsoft Access

By: Tim Wiseman   |   Comments (1)   |   Related: > Microsoft Access Integration

Problem

I created an Microsoft Access front end that points to SQL Server using linked tables, but now I need to change which database the data is being pulled from. I may need to do this many times as I move from development to test to production. What is the best way to do this?

Solution

Microsoft Access provides a quick and easy way to create a simple front end for a SQL Server database by pointing linked tables to the SQL Server. It is also relatively easy to migrate a database created in Access to SQL Server and continue to provide access to that data through Access with the "Move Data" or "Upsizing" feature in Access. There are a number of reasons to change the data source that the linked tables reference, such as moving servers.

Manually Changing Linked Tables in Microsoft Access

To change the table references, this can be done in Access using the linked table manager as shown below. If you click on Linked Table Manager a window will open like the following.

After selecting the tables that need to be updated, which is often all of them, and ensuring "Always prompt for new location" is checked, clicking "OK" will bring up a window asking for the data source to be used as shown below. You can select a DSN file or create a new one. You can also create a DSN file using the SQL Native Client.

Changing SQL Server Linked Tables Using a Form

While the linked table manager can be relatively quick for changing the destination, it remains a point of friction if you need to change the data source frequently. You may need to change the data source frequently if you are using the same front end for multiple databases or if you are moving regularly between a test and production environment. To enable a quick change, you can use the Visual Basic For Applications that is available in Access to enable changes from a menu.

For an example of how to do this, I will start with a local table that provides a list of possible database connection strings and a form to allow selection between them.

I think it is more convenient to display the FriendlyName in the form and provide the full ConnectionString directly to the VBA, rather than having to look up the ConnectionString based on the FriendlyName. So, I set the row source to a query with both columns, but set the ConnectionString as the bound column. I also set the Column Count to 2 and the Column Widths to 0,1. This is described in more detail in this tutorial.

Finally, we link the button to the VBA code. The VBA code will loop through every table and if it is a linked table it will change it to the new connection string. The simple version looks like this:

Private Sub ChangeSourceBtn_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        ' Only make a change if the table is a linked table
        If Len(tdf.Connect) Then
            tdf.Connect = Me.SourcePulldown
            tdf.RefreshLink
        End If
    Next
End Sub

Although this works quite well in simple scenarios, it is somewhat brittle in more complicated situations. While it will skip over local tables stored within the Access file, it does not account for situations where there may be linked tables pointing to more than one source. Of course, it isn't hard to add more conditionals to handle that. It also simply assumes that the connected tables exist in the newly target database. If it succeeds in connecting to the database, but finds that one of the connected tables is missing, it will generate an error like:

I have found this technique quite useful when I have a reason to switch between a test and deployment database or when there are other reasons to frequently switch between multiple databases with an Access front end.

Next Steps

• MSSQLTips has a short series dealing with Microsoft Access and SQL Server Integration
• You can also make changes to many forms at once in Access using VBA.
• Remember when working with linked tables that Access has issues adding data to tables without a primary key. It may simply refuse to add mew rows to any linked table that does not have a primary key properly defined.

About the author

Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips