Changing the server name or location of a linked Table in MS Access

Posted on by

Why is this so confusing? It isn’t but it is quite convoluded on how to get this done. I had to do this because the connections to my SQL Express database had to have the port name in the connection.

So on my local machine, I could open up the MS Access file with tables linked to the computer named CDC-EBLEY2-7\SQLEXPRESS, but if you were to open the file from someone else’s computer, an Connection failed: SQL State: ‘01000’ SQL Server Error 67 SQL Server Error: 17 (blah blah blah) resulted. Why? Because I enabled my server to be connected to via TCP/IP over port 1433 – which are the defaults, but it has to be explicitly set in the connection string of every table. What I needed was to connect to CDC-EBLEY2-7,1433\SQLEXPRESS so others could open the MS Access file and NOT get errors. This is strange, because 1433 is the default port any way – but it works.

Each one of my tables is not through an ODBC setup but each table has it’s own string which needs to be updated

Lets get some info first. Open notepad and get ready to copy the DESCRIPTION of the linked table’s construction. Here’s how

  1. Open MS Access
  2. Right mouse click on a table that used to work or you are sure does work and choose “Design View”. This does not make sense for a linked table, but bear with me.
  3. Select Yes on the warning screen to continue
  4. If, on the right, there is no properties window for the table, on the Ribbon (Access 2010) click Property Sheet
  5. This reveals a Description property – copy all that it is in that property it and paste it into Notepad or somewhere for later.
  6. Click the External Data in the ribbon and choose ‘Linked Table Manager’
    1. CLick the Always prompt for a new location check box – this is a complicated way to ask the user if (s)he wasts to change the connection info
    2. Click Select All button or choose the tables you wish to update with check marks
    3. Click OK
  7. A dialog comes up. Click New
    1. Choose SQL Server as your driver
    2. CLick the Advanced Button
    3. Paste all that stuff in Notepad
      1. EXCEPT REMOVE the TABLE=… stuff up to the next semicolon.
      2. change the server name to be CDC-EBLEY2-7,1433 where 1433 in my example is the port n
      3. Click OK
    4. It then prompts you to save all this into a file for later. Chooose a spot in My Documents in a connections folder – or better yet on a network location for other’s to use later
    5. Click OK a couple of times
    6. Now Access will replace all your tables with the new DSN (connection details) string.

 

7 thoughts on “Changing the server name or location of a linked Table in MS Access”

  1. I’m using Access 2010 as my front-end and a SQL Server back-end. I have added a table to my database and want to display it’s content in a combo box but I can’t figure out how to get the new table in my list of linked tables. Can you please help me out. Thanks in Advance…

  2. I am trying this and it is prompting me to select the dsn name for each table and I have 100s of tables. is there a way to have it just go through all of the tables without me having to press ok for each?

    • Obviously I didn’t have an answer but I should have stated that (sorry – and not just cause I am Canadian). Check this AutoHotKey solution I posted for you. http://elbsolutions.com/projects/autohotkey-example-clicking-popup/ CHeezy yes – it works like a charm to look every 10 seconds for a SPECIFIC titled popup and clicks it for you. Now you can go get a Latte knowing when you come back all will be done. Many corps block this app – so get it on a stick. It has gotten me out of lots of troubles here and there. It should not be used in production – but man it works. ALSO – you CAN automate it as an Active-X component – so for me, it works from C#, VBA etc. alleviating me from doing it by hand. You can also .exe the script (what I did) and run it as a command line from any programming language or in the background (our case).

  4. Thank you, worked perfectly, although the couple of OKs were more like 300 for me… the joy of working with databases other people made.

  5. Well written, MOST helpful. Two whole-hearted thumbs up. Had been trying to figure this out for a while now. My label program was written using ACCESS 2000, so the Linked Table Manager is under the Tool menu, but otherwise worked perfect. And I am working again, without bothering the original programmer (because I didn’t know this.)

Comments are closed.