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

Cassell

You can use the linked table manager to change connections. Use External data, ODBC to create a linked table. You can edit the connections with VBA. Example: #4928634 – Erickericka

dns-less connection string can't be changed in linked table manager. Also I have searched entire project (VBA code) and can't find the connection string which I mentioned the dsn-less connection. – Cassell

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

Erickericka

On tdf.Refresh I received error "Object doesn't support this property or method" and had to use ResfreshLink instead. – Eudemonism

@MichalHosala Thanks for taking the time to point out the error. I have corrected the code. – Furr

Just as a side note for this post, it is worth pointing out that connection strings are not quite the same in Ms Access to the likes of ASP.NET or Classic ASP, for instance - the connection string must be prefixed with 'ODBC;', and the driver specified (of particular note to those of us coming in from a .NET/.NET Core background). – Franciskus

Answer 2

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.

Jamshedpur

This is requiring me to specify the dns file for each table individually. What if I have 100 linked tables in my accdb? – Metamathematics

@Hill It seems like all tables you check in the Linked Table Manager will be updated with the new DSN. So you shouldn't have to update each one individually. – Marlborough

For me, when I do this, I get a separate dialogue to update the DSN for each individual table. I ended up finding a script that will do it for you. – Metamathematics

@Hill, where did you find the script that will do it for you? Please post! I have over a hundred tables I need to manually add the data source for. – Biogeochemistry

@Hill, it should also be noted that when it's prompting you for the connection info of every table, it is because not all of the tables you selected have the same connection string. Or in other words, if all of your tables have the same connection, Access assumes it will continue that way, and let you specify the connection just once. – Biogeochemistry