colmkav wrote:
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.
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
process.
(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 -
http://www.datastrat.com/Download2.html
- 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
Next
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 rpt.Controls
If subctl.ControlType = acSubform Then
rpt.Controls(subctl.Name).SourceObject = ""
End If
Next subctl
Reports(intx).Controls(ctl.Name).SourceObject = ""
End If
Next ctl
Reports(intx).Visible = False
DoCmd.Close acReport, Reports(intx).Name
Next
exitHandler:
Exit Sub
errHandler:
Debug.Print "Linker: closeAllObjects: " & Err.Description & " (" & Err.Number & ")"
Resume exitHandler
End Sub
--
---------------
John Mishefske, Microsoft Access MVP
