How do I "chain" linked tables in Access?

Question

My scenario: Computer A has an Access database that contains linked tables. Those linked tables actually reside in another Access database on Computer B. Nothing unusual yet.

Now we create a SQL Server database, and establish links to those tables in the Access database on Computer B; we configure a Machine DSN to define the necessary ODBC connection on Computer B. Access database B now contains both local tables and linked SQL tables.

Access database A now wants to link to Access database B's new tables -- but only its local tables show up in the dialog to add a linked table. It appears that you can't "link to a linked table" in Access...

But is this actually true? What we want to do is present database B's SQL table links to database A as if they were local tables; i.e. database A is not aware that the new tables in database B are not actually local.

Of course, we could link the SQL tables directly into database A by configuring a DSN on that computer, but we don't want to do this. We would like to use computer/database B as a nexus or "gateway" that presents both local and SQL tables seamlessly to other Access client applications on the network. This is only a temporary setup that would allow us to gradually migrate all Access client apps to SQL Server-based tables, without having to modify a lot of code.

Can this be done? Is there another workable solution or scenario we haven't thought of?

Answer 1

Nope - you can only link to real tables - you have to recreate the SQL server links you did on database B for database A

If the SQL server data does not change much and you are just using it for lookups you could import the data into real Access tables which you could link to.

EDIT

Another solution is to link the tables dynamically - that way you don't have to add the DSN manually to each computer. Use a connection string something like this:

ODBC;Driver={SQL Server};Server=<server name/IP>;Database=<database>;UID=<user>;PWD=<password>

This links a table

Dim db As Database
Dim TD As TableDef
Dim sTableName As String  ''MS Access name (can be same as SQL Server name)
Dim sServerTableName As String  ''SQL Server Name 

sTable = "Table1"
sServerTableName  = "dbo.Table1"
sServerConnect = "ODBC;Driver={SQL Server};Server=Localhost;Database=DB1;"

Set TD = db.CreateTableDef(sTableName)
TD.Connect = sServerConnect
TD.SourceTableName = sServerTableName

db.TableDefs.Append TD
db.TableDefs.Refresh