MS Access 2003刷新VBA中的链接ODBC表导致膨胀(MS Access 2003 Refresh Linked ODBC Tables in VBA Causes Bloat)

使用下面的代码，刷新链接的ODBC表连接时会扩大数据库大小。因此，如果不关闭并重新打开数据库以使其压缩，用户将永远无法完成该过程。连接从SQL链接，数据库中有13个链接表。该代码重置连接4次。

Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim rs As DAO.Recordset Dim strSite As String Set dbs = CurrentDb Set rs = dbs.OpenRecordset("tblSites") 'Run query against Default Site to create table DoCmd.OpenQuery ("qryWarranty01") 'creates tblWarranty 'Loop through Site 2, Site 3 & Site 4 and append data to table With rs .MoveFirst Do While .EOF = False strSite = rs.Fields("Site") For Each tdf In dbs.TableDefs If Len(tdf.Connect) > 0 Then tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=" & strSite & ";UID=Username; PWD=Password;" tdf.RefreshLink End If Next DoCmd.OpenQuery "qryWarranty02" 'appends to tblWarranty .MoveNext Loop End With rs.Close 'Reset tables to be linked to Default Site For Each tdf In dbs.TableDefs If Len(tdf.Connect) > 0 Then tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=Site1;UID=Username; PWD=Password;" tdf.RefreshLink End If Next

我搜索并搜索了压缩和修复之外的解决方案。有人可以向我解释为什么在这段代码中发生膨胀并且速度如此之快？还有另一种方法可以完成我需要做的事情吗？

我感谢您的帮助。

谢谢，卡拉

Using the following code, it is bloating the database size when refreshing the linked ODBC table connections. As such, the user will never be be able to finish the process completely without closing and re-opening the database for it to compact. The connections are linked from SQL and there are 13 linked tables in the database. The code resets the connection 4 times.

Dim dbs As DAO.Database Dim tdf As DAO.TableDef Dim rs As DAO.Recordset Dim strSite As String Set dbs = CurrentDb Set rs = dbs.OpenRecordset("tblSites") 'Run query against Default Site to create table DoCmd.OpenQuery ("qryWarranty01") 'creates tblWarranty 'Loop through Site 2, Site 3 & Site 4 and append data to table With rs .MoveFirst Do While .EOF = False strSite = rs.Fields("Site") For Each tdf In dbs.TableDefs If Len(tdf.Connect) > 0 Then tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=" & strSite & ";UID=Username; PWD=Password;" tdf.RefreshLink End If Next DoCmd.OpenQuery "qryWarranty02" 'appends to tblWarranty .MoveNext Loop End With rs.Close 'Reset tables to be linked to Default Site For Each tdf In dbs.TableDefs If Len(tdf.Connect) > 0 Then tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=Site1;UID=Username; PWD=Password;" tdf.RefreshLink End If Next

I've searched and searched for a resolution beyond Compacting and Repair. Can someone explain to me why the bloat is happening in this code and so fast? Is there another way to accomplish what I am needing to do?

I appreciate your help.

Thanks, Cara

最满意答案

以下是一些可能性：

忽略膨胀。它是如此极端吗？ Repair＆Compact有什么不好的？仅更改.Connect和RefreshLink qryWarranty02中使用的表，而不是全部11个表。从qryWarranty02创建SQL Server视图，链接该视图。现在你只需切换一个TableDef。使用Pass-Through查询而不是链接视图。我不认为改变PT查询的.Connect会导致与表格相同的膨胀。对4个数据库使用固定连接字符串进行4次传递查询，并循环查看它们。不再切换（但查询中有冗余）。

Here are some possibilities:

Ignore the bloat. Is it so extreme? What is so bad about Repair&Compact? Only change .Connect and RefreshLink the tables that are used in qryWarranty02, not all 11 tables. Create a SQL Server view from qryWarranty02, link that view. Now you only have to switch one TableDef. Use a Pass-Through query instead of the linked view. I don't think changing the .Connect of a PT query causes the same bloat as for a table. Have 4 Pass-Through queries with fixed connect strings for the 4 databases, and loop through them. No switching anymore (but redundancy in the queries).

更多推荐