vba 当基础 SQL Server 表的列更改时更新访问链接表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/755069/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update an Access Link Table when the underlying SQL Server table's columns change
提问by Argos
At work we've got a SQL Server database that several users connect to (read only) using Access 2003.
在工作中，我们有一个 SQL Server 数据库，多个用户使用 Access 2003 连接到该数据库（只读）。
This was fine, except now one of the tables they look at is re-created fairly often and part of this process involves a cross-tab query.
这很好，只是现在他们查看的其中一个表经常重新创建，并且此过程的一部分涉及交叉表查询。
Which means that, depending on the data, the number and and names of the columns potentially change each time the table is regenerated.
这意味着，根据数据，每次重新生成表时，列的数量和名称可能会发生变化。
However when they look at the re-created table from Access, it still shows the column headings that were there when the table was first linked to.
但是，当他们查看从 Access 重新创建的表时，它仍然显示第一次链接到该表时存在的列标题。
Is there a way I can programmatically re-link the table each time they open the Access database?
有没有办法在每次打开 Access 数据库时以编程方式重新链接表？
回答by Argos
What I ended up doing was creating a VBA function that looks something like below (needs error handling!)
我最终做的是创建一个如下所示的 VBA 函数（需要错误处理！）
Public Function ReConnectToLinkTable()
Dim db As Dao.Database
Dim tdf As Dao.TableDef
Set db = CurrentDb
Set tdf = db.CreateTableDef("local_table_name")
tdf.Connect = "ODBC;DRIVER=SQL Server;SERVER=server_name;UID=user_name;" & _
"PWD=password;APP=Microsoft Data Access Conponents;" & _
"DATABASE=database_name"
tdf.Attributes = TableDefAttributeEnum.dbAttachSavePWD
tdf.SourceTableName = "server_table_name"
db.TableDefs.Delete ("local_table_name")
db.TableDefs.Append tdf
End Function
Then I created a macro called AutoExec (the name guarantees it is called when the Access file is opened) which has an Action of RunCode, which calls the ReconnectToLinkTable() function.
然后我创建了一个名为 AutoExec 的宏（名称保证在打开 Access 文件时调用它），它有一个 RunCode 的 Action，它调用 ReconnectToLinkTable() 函数。
回答by David-W-Fenton
ODBC linked tables break when the table or view on the server is altered. Some changes can result in them just becoming read-only, others will simply not include all the columns.
更改服务器上的表或视图时，ODBC 链接表会中断。某些更改可能导致它们变为只读，而其他更改则不会包含所有列。
I have found that updating the connect string does not successfully fix this problem. It will usually fix missing fields, but it can still be read-only. The only reliable way to do this is to recreate the linked table on the fly.
我发现更新连接字符串不能成功解决这个问题。它通常会修复丢失的字段，但它仍然可以是只读的。唯一可靠的方法是即时重新创建链接表。
Another alternative would be to not use a linked table at all, but use a saved QueryDef that has the appropriate connect string. This will never have to be updated, but could be a performance issue as the metadata stored in the table link helps Access figure out how to retrieve the data. Without that metadata stored in the table link, it has to retrieve that information from the server each time the query is run.
另一种替代方法是根本不使用链接表，而是使用具有适当连接字符串的已保存 QueryDef。这将永远不必更新，但可能是性能问题，因为存储在表链接中的元数据可帮助 Access 找出如何检索数据。如果没有存储在表链接中的元数据，则每次运行查询时都必须从服务器检索该信息。
回答by Jauco
Something like this snippetis usually used. Search google for 'ms access refresh link table' and you'll find various solutions all similar to this one.
通常使用像这个片段这样的东西。在谷歌上搜索“ms access refresh link table”，你会发现各种与此类似的解决方案。
