Programmatically changing SQL Server linked tables in Microsoft Access

By:   |   Updated: 2015-11-11   |   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.

LinkedTableManager1

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.

SelectDataSource

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.

DataSourcesTable

SelectSourceForm

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:

Error3011

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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Wiseman 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


Article Last Updated: 2015-11-11

Comments For This Article




Friday, July 2, 2021 - 4:13:54 AM - Anand Jose Back To Top (88935)
Is it possible to change the SQL Server Linked Tables Using Python?














get free sql tips
agree to terms