Access VBA application: Placed in a State that prevents it from being opened or locked

cipriano555
cipriano555 used Ask the Experts™
on
I am developing an Access 2013 application. In testing, the first time I run through my process, it works. When I try to run the application a second time, I get the message,   "The database has been placed in a state...that prevents it from being opened or locked."  

I am developing this on my laptop.  There is no network, or multiple users involved.  The application retrieves data from a SQL Server database and copies it into an Access table.   The error shown in the attached image occurs at this statement:   cnDest.Open CurrentProject.Connection

  Dim cnDest As New ADODB.Connection
 cnDest.Open CurrentProject.Connection

If I close access and open it up again, the error is gone and I can run the process again...but that is not a fix for the problem.  I researched on the web...nothing conclusive.

Thanks for your help
PlacedInAStateError.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Just use CurrentProject.Connection   There is no need to use cnDest .

Jim.
Top Expert 2016

Commented:
are you closing the cnDest after using it?

cnDest.close

Author

Commented:
Yes, I close it...the end of my procedure has

SourceRs.Close
Set SourceRs = Nothing
cnDest.Close
Set cnDest = Nothing

I will try to redo things without even using cnDest
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
Keep in mind that the point of using CurrentProject.Connection is that you get to use Access's own ADO connection object, so you don't need to do your own.

If you want your own because you want to change the connection properties, then you'd specify the provider and all the properties, not use Access's connection.

Jim.

Author

Commented:
I got rid of cnDest and just used CurrentProject.Connection directly, problem solved.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial