The key is the Connection String. And the problem with my solution is it will be different than yours because of the connection string. Do you manually do it now? Do you use a DSN?
If so then design a linked table, you will get a warning that you can't change some properties, but continue. Then look at the properties when you have the design view of the table. One of the properites of the table is the connection string.
Using that and DoCmd.TransferDatabase you should be able to figure it out. I had a table with all of the tables that I want to link to. So I looped through the table and then call TransferDatabase with the appropriate settings.
It may also help you to try to create a macro to connect to one table (for some reason it helps me with what parameter gets what value).
This is the Access help topic of the manual process I'm trying to automate:
Open an Access file, or switch to the Database window for the open Access file.
Do one of the following:
To import tables, on the File menu, point to Get External Data, and then click Import. To link tables, on the File menu, point to Get External Data, and then click Link Tables.
In the Import (or Link) dialog box, in the Files of type box, select ODBC Databases(). The Select Data Source dialog box lists the defined data sources for any ODBC drivers that are installed on your computer.
Click either the File Data Source or Machine Data Source tab, and then double-click the ODBC data source that you want to import.
To define a new data source for any installed ODBC driver, click New, and then follow the instructions in the Create New Data Source dialog box and the dialog boxes that follow it before continuing.
If the ODBC data source that you selected requires you to log on, enter your logon ID and password (additional information might also be required), and then click OK. Microsoft Access connects to the ODBC data source and displays the list of tables that you can import or link.
If you're linking a table, select the Save The Login ID And Password check box to save the information for the table in the current Access database so that users won't have to enter it. If you leave the check box cleared, all users must enter the logon ID and password every time they open the table in each new session with Microsoft Access. Your SQL database administrator can also choose to disable this check box, requiring all users to enter the logon ID and password each time they connect to the SQL database.
Click each table that you want to import or link, and then click OK. If you're linking a table and it doesn't have an index that uniquely identifies each record, then Microsoft Access displays a list of the fields in the linked table. Click a field or a combination of fields that will uniquely identify each record, and then click OK.