accessing a database

accessing a database

Post by colmka » Fri, 04 May 2007 22:39:41

Hi, could someone tell me how I can check whether a database is open
by name

eg something like db("mydbname")

accessing a database

Post by Tom van St » Fri, 04 May 2007 23:11:19

No idea what you are trying to achieve, but if a database is open,
there is an LDB file which you could test for (using the Dir command).
A more elaborate function that would get you the machine name is here
(from a recent post):
(That page also has a link to the Jet UserRoster in Access 2000 and
(The bottom of this page has the details on using the DLL)



accessing a database

Post by colmka » Fri, 04 May 2007 23:24:17

actually the problem I have is that I want to compact a db when it
reaches a size but I get error saying the db is open exclusively to
another user (ie through the same process). The db is only open as far
as I know through linked tables. my code never opens it directly.

accessing a database

Post by John Mishe » Sat, 05 May 2007 14:21:37

olmkav wrote:

You'll need to close any object in the Front End table that references a linked table in
your Backend. As long as a reference to a data source (table/query) in the backend exists
(perhaps a form or variable in code) you will not be able to gain exclusive use of the BE
.mdb file.

One possible solution is to iterate through all your objects and close them except the
form from which you might need to run the compact. This should break the link to the
back-end and cause removal (deletion) of the .ldb lock file. At this point your app should
open the BE Exclusively and do the compact. If the .ldb lock file exists you are not going
to get exclusive use of the related .mdb file.

Here is part of the solution. This is test code; you should add some better error handling
and you may need to prevent the closing of a form that you want visible during the compact

(That form should be an un-bound one if you want to keep it open. Or, rather, it shouldn't
bind to any data source in the BE .mdb you want to compact.)

But, this code sample just breaks connections between a front-end (FE) and back-end (BE).
If your application is multi-user then this code won't guarantee you can get exclusive use
of the BE .mdb.

There are many approaches you can take with a multi-user application. You could kick all
users out of the database (DataStrat's KickEmOff -
- Arvin Meyer) and then run this code and finally your compact routine.

You might also use a more opportunistic approach where you attempt to gain exclusive
access of the BE .mdb and if you can't you fail silently knowing that you'll eventually
get exclusive access. If you application is in use (or at least logged into with a
reference to a linked table in the BE) 24 x 7 then this approach isn't going to work.

At any rate, hope this helps a bit.

Private Sub closeAllObjects()
On Error GoTo errHandler

' close any forms except the one that called this class
Dim intx As Integer
Dim intCount As Integer
Dim ctl As Control, subctl As Control
Dim frm As Access.Form, rpt As Access.Report

' close all open forms
intCount = Forms.Count - 1

For intx = intCount To 0 Step -1
If Forms(intx).Name <> m_sCallingForm Then
' close any subforms
For Each ctl In Forms(intx).Controls
If ctl.ControlType = acSubform Then
Set frm = Forms(intx).Controls(ctl.Name).Form
' search the subform for subforms
For Each subctl In frm.Controls
If subctl.ControlType = acSubform Then
frm.Controls(subctl.Name).SourceObject = ""
End If
Next subctl

Forms(intx).Controls(ctl.Name).SourceObject = ""
End If
Next ctl
Forms(intx).Visible = False
DoCmd.Close acForm, Forms(intx).Name
End If

intCount = Reports.Count - 1

For intx = intCount To 0 Step -1
' close any subreports
For Each ctl In Reports(intx).Controls
If ctl.ControlType = acSubform Then
Set rpt = Reports(intx).Controls(ctl.Name).Form
' search the subform for subforms
For Each subctl In

accessing a database

Post by Arno » Sat, 05 May 2007 18:32:15

Hi John,

Wouldn't it be sufficient to just close the open forms and reports?
I mean: Why are you testing for subforms and subreports?
They are not part of the collection ... or??

I am using code like this for years to close all forms:
Do Until Forms.Count = 0
FormName = Forms(intAantalForms - 1).Name
DoCmd Close acForm, FormName

Same thing for reports I guess.

Arno R

accessing a database

Post by John Mishe » Sun, 06 May 2007 17:12:19

Hi Arno. I pulled this from an older project. I recall that I added the subform/subreport
code after my initial implementation in response to some bug/problem but I can't recall
what it was and, unfortunately, I didn't document the reason.

I agree with you; closing the form would also close the form in the subform control but I
distinctly remember going back and adding the code for the subform/subreports after some
problem made me go back and review the code.

If I get some time I'll try to determine the reason. At first I thought it might be that
the code is from VB6 where I have implemented a class to support subforms but this line

Dim frm As Access.Form, rpt As Access.Report

pretty much confirms it was from Access.

John Mishefske, Microsoft Access MVP

accessing a database

Post by Arno » Sun, 06 May 2007 20:37:53

Hi John,

I did some testing with open forms that have subform controls (several levels deep)
Sub test()
Dim i As Integer, msg As String
For i = 0 To Forms.Count - 1
msg = msg & vbNewLine & Forms(i).Name
Next i
MsgBox "Open forms:" & msg
End Sub

The subforms are not mentioned.
Only when I open a subform seperately *as a form*, then it is part of the collection of open forms.

I would be very interested if you could determine your reason for the subform/report test.

Arno R

accessing a database

Post by Larry Lins » Mon, 07 May 2007 10:45:45


Just for the record, there is no such object as a "Subform" or a "Subreport"
in Access; there is a "Subform Control" and a "Subreport Control" into which
you may embed Forms. The embedded Form, when it is displayed in the Subform
Control does not exist as an instance of the Form (e.g., is not "Open"), and
thus is not in the Forms Collection (which contains only Open Forms). The
same applies to Reports and Subreport Controls.

It exists as the Form property of the Subform Control, and that is the
(only) appropriate way to refer to it.

I'm not aware of all the considerations that entered into the design
decisions in this area, but that's the way it works.

Larry Linson
Microsoft Access MVP

accessing a database

Post by John Mishe » Mon, 07 May 2007 13:09:15

Yes, Larry pointed this out as well. Forms opened in a subform control are not considered
an open form in the Forms collection.

I can't recall the reason; the only thing I can surmise is that perhaps the form in the
subform control is bound and the parent isn't. But I would still assume closing the parent
form would close the form in the subform control and thus close the binding to the data
source. If I had more time right now I'd test that. If I run across the reasoning I'll
post back.

Also, I do seem to recall that there were tertiary forms and that was part of the issue
and why that code went to that level.

But obviously, since I can't justify my reasoning, we'll have to call it programmer error
or at least oversight for not properly documenting the reason.

John Mishefske, Microsoft Access MVP