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
- Open MS Access
- 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.
- Select Yes on the warning screen to continue
- If, on the right, there is no properties window for the table, on the Ribbon (Access 2010) click Property Sheet
- This reveals a Description property – copy all that it is in that property it and paste it into Notepad or somewhere for later.
- Click the External Data in the ribbon and choose ‘Linked Table Manager’
- 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
- Click Select All button or choose the tables you wish to update with check marks
- Click OK
- A dialog comes up. Click New
- Choose SQL Server as your driver
- CLick the Advanced Button
- Paste all that stuff in Notepad
- EXCEPT REMOVE the TABLE=… stuff up to the next semicolon.
- change the server name to be CDC-EBLEY2-7,1433 where 1433 in my example is the port n
- Click OK
- 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
- Click OK a couple of times
- Now Access will replace all your tables with the new DSN (connection details) string.