我如何“链接"到Access中链接表? [英] How do I "chain" linked tables in Access?

问题描述

我的情况:计算机A具有包含链接表的Access数据库.这些链接表实际上位于计算机B上的另一个Access数据库中.

现在，我们创建一个SQL Server数据库，并在计算机B的Access数据库中建立到这些表的链接；我们配置了计算机DSN，以在计算机B上定义必要的ODBC连接.现在，访问数据库B既包含本地表又包含链接的SQL表.

Access数据库A现在想要链接到Access数据库B的新表-但是在对话框中仅显示其本地表以添加链接表.看来您无法在Access中链接到链接表" ...

但这是真的吗?我们想要做的是将当前数据库B的SQL表链接到数据库A ，就好像它们是本地表一样；即数据库A不知道数据库B中的新表实际上不是本地的.

当然，我们可以通过在那计算机上配置DSN来将SQL表直接链接到数据库A中，但是我们不想这样做.我们想将计算机/数据库B用作一个连接或网关"，将本地表和SQL表无缝地呈现给网络上的其他Access客户端应用程序.这只是一个临时设置，它使我们能够逐步将所有Access客户端应用程序迁移到基于SQL Server的表，而无需修改大量代码.

可以做到吗?还有我们没想到的可行的解决方案或方案吗?

解决方案

不是-您只能链接到实际表-您必须为数据库A重新创建在数据库B上执行的SQL Server链接

如果SQL Server数据变化不大，而您只是将其用于查找，则可以将数据导入到可以链接到的真实Access表中.

编辑

另一种解决方案是动态链接表-这样，您不必手动将DSN添加到每台计算机.使用如下所示的连接字符串:

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

这链接了一个表

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

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?

解决方案

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

这篇关于我如何“链接"到Access中链接表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！