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数据库时以编程方式重新链接表?
3 个解决方案
#1
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()函数。
#2
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找出如何检索数据。如果没有存储在表链接中的元数据,则每次运行查询时都必须从服务器检索该信息。
#3
Something like this snippet is usually used. Search google for 'ms access refresh link table' and you'll find various solutions all similar to this one.
通常使用类似于此片段的内容。搜索谷歌的'ms访问刷新链接表',你会发现各种类似于这个的解决方案。
#1
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()函数。
#2
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找出如何检索数据。如果没有存储在表链接中的元数据,则每次运行查询时都必须从服务器检索该信息。
#3
Something like this snippet is usually used. Search google for 'ms access refresh link table' and you'll find various solutions all similar to this one.
通常使用类似于此片段的内容。搜索谷歌的'ms访问刷新链接表',你会发现各种类似于这个的解决方案。