Linked table ms access 2010 change connection string

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

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