通过 VBA 运行插入查询时 MS-Access 数据库锁定 [英] MS-Access Database Locking when running a Insert Query via VBA

问题描述

我正在创建一个简单的数据库.我有一个功能，我只是创建了一个 Insert Into 查询并将其保存在数据库中.我想做的是在 VBA 代码中调用该查询.

I am creating a simple database. I have a functionality where i have simply created a Insert Into query an saved it in the database. what i want to do is, call that query in VBA code.

我已经完成了以下编码:

I have done following coding :

Private Sub Update_ISO_Review_Register()
  Dim dbs As DAO.Database

  Set dbs = CurrentDb

  dbs.Execute "Update_ISO_Review_Register_ApplicationData"

  dbs.Close
  Set dbs = Nothing
End Sub

代码有效并执行查询.问题是之后它锁定了数据库并给出了以下错误.

The code works and it executes the query. Issue is that after that it locks the database and it gives the following error.

您目前没有对数据库的独占访问权限.如果你继续进行更改，您以后可能无法保存它们.

You do not have exclusive access to the database at this time. if you proceed to make changes, you may not be able to save them later.

Update_ISO_Review_Register_ApplicationData 查询的 SQL 是:

The SQL for the Update_ISO_Review_Register_ApplicationData query is:

INSERT INTO     ISO_REVIEW_REGISTER ( SLTF_Ref, Brand, Application_No ) 
SELECT DISTINCT b.matter_No AS SLTF_Ref
                , b.Brand
                , b.CREDIT_APPLICATION_ID AS Application_No 
FROM            WBC_HFM_FileReveiw_Table AS a INNER JOIN WBC_HFM_Application_Table AS b ON 
                    a.CREDIT_APPLICATION_ID = b.CREDIT_APPLICATION_ID;

有什么帮助吗?

推荐答案

如果你只这样做会有效吗:

Would it work if you only do this:

Private Sub Update_ISO_Review_Register()    
    CurrentDb.Execute "Update_ISO_Review_Register_ApplicationData"        
End Sub

没有这些你也可以过得很好:

You can nicely live without these:

  Dim dbs As DAO.Database
  Set dbs = CurrentDb
  dbs.Close
  Set dbs = Nothing

特别设置set dbs = Nothing，它是一个局部变量，在End Sub之后消亡".

especially set set dbs = Nothing, which is a local variable and "dies" after End Sub.

这篇关于通过 VBA 运行插入查询时 MS-Access 数据库锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！