Database Locking when going to form design view

[ES]

I am working on a database with one form (several tabs). The form runs
several routines using the standard DAO connection when it loads. One tab
sets up an ADO connection to the current database. This has to be initiated
by a user click. (I need the ADO functionality for this process) The
database runs fine when I open the form directly into form view but it I take
it to design view then back to form view the .open command for the ADO
connection results in the following error:

“The database has been placed in a state by user ‘Admin’ on machine
‘1233455…†that prevents it from being opened or lockedâ€

It does not matter if the routine for the ADO connection has been run or not
before going to design view and back for the error to occur. To fix the
error, I have to close the form and reopen in form view.

What is happening when I go into design view that is resulting in the db
being locked? And what can I do to stop it?

Other info:
Running Access 2003
No security set on the databae
Default open mode = shared
Default record locking = no locks
Database is on my machine and there are no other users

 

[Bill Mosca, MS Access MVP]

The open event does not run if you switch from form view to design view and
back to form view again. Maybe that's what is hosing your form.

I put an code for opening in the foem's Load event so it always fires when
switching modes.

 

[ES]

The code is in the load event. I tried commenting it out so that no code
runs when the form is opened and I get the same result.

No error if I just go to form view
Error if I go [design view to form view] or [form view to design view to
form view]

in both of the above cases, no code is running until I click the button to
run the ADO procedure in the finial opening of the form in form view. in the
[form view to design view to form view], I am not running the code to make
the ADO connection until the second time in Form View. As a result of this,
I don't think it is the ADO connection that is causing the problem. I'm
thinking Access is locking the file somehow when I go to design view and not
releasing it when I go to form view.

Any other thoughts? I really need to get this fixed as it is a PAIN to have
to close the form everytime I change something in this procedure and want to
test it.

 

[Bill Mosca, MS Access MVP]

Are you cleaning up your objects (ie setting them to Nothing when
appropriate)?

Maybe if you posted the code someone could spot the problem.

 

[ES]

I do not think I have any open objects when the routine is done. I also
don't think that it is the code because I get the problem the first time the
code runs if I open the form from design view so there should not be any open
objects even if I did not close them in code.

Just to make sure I am being clear - If I open the form in form view first,
I can run the process as much as I want with out problems. However, if I go
into design view either first or from form view, when I go back to form view,
I get the error. It does not matter if the routine was run before entering
design view or not.

Here is the connection code and clean up. there are a bunch of rst that are
not listed but they are handled the same.

Dim conn As ADODB.Connection
Dim rstMSD As ADODB.Recordset
Dim fld As Field
Dim objNode1 As Node
Dim objNode2 As Node


'Create the ADO Connection object
Set conn = New ADODB.Connection

'Specify a valid connection string

strConn = "Data Provider=Microsoft.Jet.OLEDB.4.0;"
strConn = strConn & "Data Source = " & _
CurrentProject.Connection.ConnectionString
conn.ConnectionString = strConn
gblbooCurrentYrData = True

'Specify the Data Shaping provider
conn.Provider = "MSDataShape"

'Open the connection
conn.Open

[do a bunch of stuff with a SQL Shape Stmt - add records to treeview, ....]

'clean up code

conn.Close
set rstMSD = nothing
Set conn = Nothing
Set fld = Nothing
Set objNode1 = Nothing
Set objNode2 = Nothing