MS-Access Database Locking when running a Insert Query via VBA

Question

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.

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;

Any help ?

Without the SQL of that query, we can't know what's happening. At least remove the dbs.Close, CurrentDb is a reference to your current database connection, and you don't want to close that. — Erik A
– Erik A, Commented Jun 12, 2018 at 7:23
That is not locking the database; just the opposite. It needs to lock the database but it cannot. — ashleedawg
– ashleedawg, Commented Jun 12, 2018 at 7:24
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; — Nash
– Nash, Commented Jun 12, 2018 at 8:10
dbs.Close - don't. You never do this, except for DBs you explicitly opened with OpenDatabase. — Andre
– Andre, Commented Jun 12, 2018 at 8:16

Vityata · Accepted Answer · 2018-06-12 08:54:02Z

1

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

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

answered Jun 12, 2018 at 8:54

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MS-Access Database Locking when running a Insert Query via VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related