Linked table ms access 2010 change connection stri

Question

I am working on a existing MS Access 2010 project that has a linked table link to Sql Server database.

When I mouse over to the linked table I can see a connection string 'ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user'

This looks like a dsn-less linked table.

Question

1. Where the connect string locate at? How to change it (example database name)?

2. How can I create a similar dsn-less linked table? Anytime when I create a linked table Access 2010 always force me to choose\create a dsn (file or machine).

Answer 1

You can change the connection string using the following guide (Original Source).

Firstly, get the existing connection string.

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”.
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.

Secondly update the connection string.

1. 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
2. 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
      • EXCEPT REMOVE the TABLE=… stuff up to the next semicolon.
      • Change the server name
      • 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.

Answer 2

To print all connection strings:

Dim tdf As TableDef
Dim db As Database

    Set db = CurrentDb

    For Each tdf In CurrentDb.TableDefs
        If tdf.Connect <> vbNullString Then
           Debug.Print tdf.Name; " -- "; tdf.SourceTableName; " -- "; tdf.Connect
        End If
    Next

To create a linked table:

With CurrentDb
    ''If the table does not have a unique index, you will need to create one
    ''if you wish to update.
    Set tdf = .CreateTableDef("LocalName")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.SourceTableName = "TABLE_NAME"
    .TableDefs.Append tdf
    .TableDefs.Refresh
End With

To change a link:

    Set db = CurrentDB
    Set tdf = db.TableDefs("MyTable")
    tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=10.0.0.1;" _
      & "UID=testdb;APP=Microsoft Office 2003;WSID=abc;TABLE=dbo.user"
    tdf.RefreshLink