时间戳

首页 > 解决方案 > 关闭使用附加查询打开的 MS Access laccdb 文件

database - 关闭使用附加查询打开的 MS Access laccdb 文件

问题描述

我正在访问前端文件中从 VBA 运行附加查询。该查询在另一个未连接到前端的数据库中的表上运行。（它是来自连接后端的数据存档）这里是查询：

DoCmd.RunSQL "INSERT INTO " & tblNames(i) & " IN '" & archiveFile & "' " & _
                "SELECT " & tblNames(i) & ".* " & _
                "FROM " & tblNames(i) & ""

我正在使用一个数组来循环浏览要归档的每个表。并且“archiveFile”变量作为参数传入，具体取决于我需要将数据发送到哪个 accdb 文件。

我的问题是： sql 语句有效，但完成后存档文件仍处于锁定状态。换句话说，为我附加到的存档文件打开了一个 laccdb，它会保持打开状态，直到我关闭前端（我从中运行 vba 的文件）

我的问题; 有没有办法关闭由“DoCmd.RunSQL”建立的连接，我可以告诉我，我没有打开任何可以关闭的东西。但是正在创建一些东西并悬而未决...在此先感谢。如果需要，我很乐意澄清或分享我的更多代码。（如果有更好的方法，我会全力以赴）

更新：

我尝试从 gui 而不是从 VBA 代码运行查询，我得到了相同的结果。laccdb 文件已创建并且无法删除，除非我关闭运行查询的前端。我希望有一种方法可以调用打开的连接然后关闭它们。

标签： databasevbams-access

解决方案

I have been playing around with the data connection, not only with the archive file (which is not linked to the front end) but also with the back end which is linked. I found this article performanceldblocking about improving performance by maintaining a permanent link with the back end file. After I did that, somehow, although it seems somewhat unrelated to me, the archive lock file doesn't stick. it blinks in an out as the various queries run and when they are finished archiving data, the lock file is gone and no longer causes issues.

again, I am not sure exactly why but setting up a persistent connection to the back end seems to have solved the issue so that the archive file is not confused about my front end still being connected to it.

推荐阅读