Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Programmatically changing SQL Server linked tables in Microsoft Access


By:   |   Read Comments   |   Related Tips: More > 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.

Linked Table Manager

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.

Select Data Source Window

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.

Data Sources Table

Select Source Form

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.

PullDownProperties

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:

PullDownProperties

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


Last Update:






About the author
MSSQLTips author Tim Wiseman Tim Wiseman is a SQL Server DBA and Python Developer in Las Vegas.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools