Test If a DB Is Open

[Eddie's Bakery and Cafe']

Hi, I am using ADODB methods to open and read a database. In doing so, I
frequently get run-time errors that tell says the database is "Locked". I
would like to add logic before the db open statement (i.e., cnn.open) that
determines if the database is open or locked.

I would appreciate any feedback on how to test the status of a database. A
code sample would be great.

The ISSUE: For some unknown reason, when I switch between “Form-View†and
“Design-Viewâ€, I get a nasty run-time error. The error message is: “The
database has been placed in a state by user ‘Admin’ on machine … that
prevents it from being opened or locked.

I indirectly initiate a db open operation from the “Form_Load()†event
routine. In the Form_Load() sub, I call another routine (in another class
module) that does the actual db open.

If you have any idea what is going on, I would really appreciate your
feedback.

Thanks,

 

[Nick Coe \(UK\)]

In Eddie's Bakery and Cafe' typed:

Hi, I am using ADODB methods to open and read a database.
In doing
so, I frequently get run-time errors that tell says the
database is
"Locked". I would like to add logic before the db open
statement
(i.e., cnn.open) that determines if the database is open
or locked.

I would appreciate any feedback on how to test the status
of a
database. A code sample would be great.

The ISSUE: For some unknown reason, when I switch between
"Form-View"
and "Design-View", I get a nasty run-time error. The
error message
is: "The database has been placed in a state by user
'Admin' on
machine . that prevents it from being opened or locked.

I indirectly initiate a db open operation from the
"Form_Load()" event
routine. In the Form_Load() sub, I call another routine
(in another
class module) that does the actual db open.

If you have any idea what is going on, I would really
appreciate your
feedback.

Thanks,

Eddie,

When an object in any version of Access later than A97 is
opened in Design View it _always_ locks that database in
exclusive mode. From A2000 onwards the way object
definition data (sometimes called metadata) is stored within
the database changed forcing the requirement for an
exclusive lock when in design mode.

Admin with a blank password is the default user if security
is not set in Access, hence the message.

 

[Eddie's Bakery and Cafe']

Hi Nick, Thanks for the info. Is there anyway of getting around this
problem. Because every time I run my program (via switching to "Form-View")
after making software changes, I get this error. Currently, I have to make
my software changes, kill the VB Editor, Kill the Form and re-open the form
to run the program. There has to be a better way of developing code.

Thanks, Eddie

 

[Nick Coe \(UK\)]

In Eddie's Bakery and Cafe' typed:

Hi Nick, Thanks for the info. Is there anyway of getting
around this
problem. Because every time I run my program (via
switching to
"Form-View") after making software changes, I get this
error.
Currently, I have to make my software changes, kill the VB
Editor,
Kill the Form and re-open the form to run the program.
There has to
be a better way of developing code.

Thanks, Eddie

Eddie,

That's a new one on me. Are you absolutely _certain_ that
all objects that were open in design view are now closed?

Sometimes the VBA editor will open umpteen windows all by
itself (actually it depends what you were doing when you
closed it, but I hate that kind of assumptive behaviour) and
you won't notice because they're hidden behind the one
you're working on.

Could be a persistant lock in the applications' LDB file.
Close your app and Access, double check it _is_ closed and
then look in your app folder for a file with the same name
as your app and the suffix .ldb. If it exists delete it.
Don't do this with your db open!

Double check your options, make certain you've got your app
set to share.

When you set connection are you using
CurrentProject.Connection or creating a new connection?
Wouldn't have thought it was an issue but I've always used
CurrentProject.Connection or CodeProject.Connection, it's a
lot easier.

Make sure you close connections and recordsets and set to
Nothing when done with.

Can't think of anything else at the moment...

 

[Eddie's Bakery and Cafe']

Hi Nick, Thanks for the response and suggestions, I am doing everything you
suggested, except think I am creating a new connection every time I enter the
sub. Below is a snapshot of my code:

Public Sub myDBMgr (...)
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
...
Set cnn New ADODB.Connection
Set rs = New ADODB.Recordset
...
cnn.Open strCnn
rs.Open strSQL, cnn
...
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
End Sub

Thanks for help,

Eddie

 

[Nick Coe \(UK\)]

Eddie,

I can't think of anything alse to try other than using
CurrentProject.Connection. What's in strCnn, it's not
trying to open exclusive by any chance? Re-read the help on
ADO connection settings might set you on the right trail.

Here's a chunk of code from AccHelp that uses the ADO
current connection:-

'*******************************
Public Function GetTipsFlag(strTblFl as String) As Boolean
'Retrieve the show setting for Tips
'Nick Coe 22 Mar 04 v1

Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

On Error GoTo HandleErr
' Must use CodeProject to see table when an add-in else
use CurrentProject
Set cn = CodeProject.Connection
Set rst = New ADODB.Recordset

' Eddie - change the keyset, Lock etc to suit your
circumstances.
' This particular function only reads a value (like
DLookup does).
rst.Open strTblFl, cn, adOpenStatic, adLockPessimistic,
adCmdTableDirect

GetTipsFlag = rst!ShowTips

ExitHere:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not cn Is Nothing Then
Set cn = Nothing
End If

Exit Function

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.description,
vbCritical, "basAccHelp.GetTipsFlag"
End Select
Resume ExitHere

' End Error handling block.
End Function
'*************************************

If checking your strCnn and trying to re-use the current
connection fails then I suggest you post a new message in
this ng with a subject something like 'New ADODB connection
to Jet fails...'

Good luck

--
Nick Coe (UK)
AccHelp v1.01 Access Application Help File Builder
http://www.alphacos.co.uk/ Download Free Demo Copy

In Eddie's Bakery and Cafe' typed:

 

[Brendan Reynolds]

That code should certainly close the connection provided the code executes
normally, without error. But it will leave the connection open if an error
occurs, or if you reset the code in break mode during debugging.

When debugging in break mode, you can place the insertion point in the
'rs.Close' line, press Ctrl+F9 to make that the next statement, and press F5
to run the code from that line, closing the recordset and the connection.
(Or just go to the Immediate window, and type 'rs.Close' and Enter, then
'cnn.Close', and Enter).

To ensure that the recordset and the connection are closed if an error
occurs, I use code such as this ...

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

On Error GoTo ErrorHandler
Set cnn = New ADODB.Connection
cnn.ConnectionString = strConnection
cnn.Open
Set rst = New ADODB.Recordset
rst.Open strSQL, cnn
'...
rst.Close
cnn.Close

ExitProcedure:
On Error Resume Next
If Not rst Is Nothing Then
If rst.State <> adStateClosed Then
rst.Close
End If
End If
If Not cnn Is Nothing Then
If cnn.State <> adStateClosed Then
cnn.Close
End If
End If
On Error GoTo 0
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly Or
vbInformation, "Test Code"
Resume ExitProcedure

 

[Eddie's Bakery and Cafe']

Thanks for all the feedback, it was very helpful