Adding linked table to Access 2003 while keeping ODBC connection info in MDB

Question

I have an Access 2003 database MDB where all of the tables exist as linked tables within SQL Server 2005. The MDB file contains all of the ODBC information that points to the correct SQL Server and log-on credentials (trusted connection).

What I would like to do is add a new linked table to the MDB file however I am not sure how to go about specifying the ODBC connection information. When I try to add a new linked table I keep getting prompted to locate or create a DSN file. I don't want to have to create a new DSN entry on every machine, rather I would like all that information stored within the Access MDB file itself.

In the existing database I can "hover" over the table names and see the ODBC connection info as a tool-tip. All I need to do is add another linked table using the same connection information.

I do have access to the SQL Server where the tables are linked to,. I have already created the new table I wanted to add. I just need to find a way to link to it.

Answer 1

You can use the connection string from an existing table, or you can do something like:

''This is a basic connection string, you may need to consider password and so forth
cn = "ODBC;DSN=TheDSNName;Trusted_Connection=Yes;APP=Microsoft Office 2010;DATABASE=TheDatabaseName;"

There are a few was to connect a table:

sLocalName = "TABLE_SCHEMA" & "_" & "TABLE_NAME"

With CurrentDb
    If DLookup("Name", "MSysObjects", "Name='" & sLocalName & "'") <> vbNullString Then
        If .TableDefs(sLocalName).Connect <> cn Then
            .TableDefs(sLocalName).Connect = cn
            .TableDefs(sLocalName).RefreshLink
        End If
    Else
        ''If the table does not have a unique index, you will neded to create one
        ''if you wish to update.
        Set tdf = .CreateTableDef(sLocalName)
        tdf.Connect = cn
        tdf.SourceTableName = "TABLE_NAME"
        .TableDefs.Append tdf
        .TableDefs.Refresh
    End If
End With

This will produce a message box if the table does not have a unique index

  DoCmd.TransferDatabase acLink, "ODBC Database", cn, acTable, "TABLE_NAME", sLocalName

Answer 2

I was able to add the table successfully and wanted to detail the steps here.

1. I added the new table by clicking "New" within Access and chose "Link Table"
2. When prompted with the Link file dialog I chose ODBC from the file type list box
3. I created a new DSN item (only used for initial linking to table)
4. Proceed with creating DSN and you will follow process of linking to the table created in SQL Server
5. Table should show up in Access

I then created the following sub routine in the code module. It essentially loops through all your tables in Access that have ODBC connections and sets the proper ODBC connection info into the Table definitions. You can delete the DSN you created previously as it is no longer needed.

Public Sub RefreshODBCLinks()

    Dim connString As String
    connString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
             "SERVER=<SQL SERVER NAME>;UID=<USER NAME>;" & _
             "Trusted_Connection=Yes;" & _
             "APP=<APP NAME>;DATABASE=<DATABASE NAME>"

    Dim db As DAO.Database
    Dim tb As DAO.TableDef
    Set db = CurrentDb
    For Each tb In db.TableDefs
    If Left(tb.Connect, 4) = "ODBC" Then
        tb.Connect = connString
        tb.RefreshLink
        Debug.Print "Refreshed ODBC table " & tb.Name
    End If
    Next tb
    Set db = Nothing

End Sub

NOTE ... To execute the above SubRoutine I just typed in it's name into the "immediate" windows within the Access code module. You could also create a macro that executes this routine and then whenever you create a new table you could just run the macro.

Thanks to "Remou" for his answer and assistance!

P.S. If you are interested in APP=<APP NAME> in the connection string and what it is for check out this article. http://johnnycoder.com/blog/2006/10/24/take-advantage-of-application-name/

Answer 3

For what it's worth, I'm lazy. I keep a DSN on my development machine, and use it to create new linked tables. I then run Doug Steele's code to convert the links to dsnless connections before distribution of the front end to the end users.