TEALTEAL
asked on
Compact Repair Database (Close Database)
I have the code to compact and repair a database. But I receive the error 3356 the database is open.. What is the best way to close the database, if it is open. I have tried db.Close but it must be open somewhere else in the project. Any suggestions?
ASKER
Thanks, I've tried that but its still giving the error.
I'd be hesitant about declaring recordsets as public. Public variables have their place, but I have had numerous problems with recordsets that are declared public. The data tends to change underneath you.
Try the following instead :
Dim ws As Workspace
Dim Db As Database
Dim Rs As Recordset
For Each ws In Workspaces
For Each Db In ws.Databases
For Each Rs In Db.Recordsets
Rs.Close
Set Rs = Nothing
Next
Db.Close
Set Db = Nothing
Next
ws.Close
Set ws = Nothing
Next
Try the following instead :
Dim ws As Workspace
Dim Db As Database
Dim Rs As Recordset
For Each ws In Workspaces
For Each Db In ws.Databases
For Each Rs In Db.Recordsets
Rs.Close
Set Rs = Nothing
Next
Db.Close
Set Db = Nothing
Next
ws.Close
Set ws = Nothing
Next
Sorry, forgot to mention, declaring the Db object as public should be fine though.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It would work across multiple forms/modules if an object was declared as type workspaces and set to dbengine.workspaces
That should go through any databases opened on workspaces in the workspaces collection for the current application.
It wouldn't do datacontrols etc. though.
It works regardless of what workspace, so long as the workspace if properly appended to the workspaces collection.
If not workspace is explcitly used when opening a database I believe workspaces(0) is used, which is automatically placed in the workspaces collection (I think).
It also works independently of the database, seeing as the database connections are attached to a workspace, and each workspace has its connections closed.
In short, it should work for all connections that are attached to workspaces in the workspaces collection.
We use this code in a module of an App with about 30/40 forms, 5 classes and 4 modules. It successfully closes ALL of our database connections... but we don't use data controls etc. and mostly we don't explicitly create workspaces, we just use workspaces(0). In those cases where we do create a workspaces, we always go Workspaces.Append Ws etc.
.... Nothing wrong with your answer though.
That should go through any databases opened on workspaces in the workspaces collection for the current application.
It wouldn't do datacontrols etc. though.
It works regardless of what workspace, so long as the workspace if properly appended to the workspaces collection.
If not workspace is explcitly used when opening a database I believe workspaces(0) is used, which is automatically placed in the workspaces collection (I think).
It also works independently of the database, seeing as the database connections are attached to a workspace, and each workspace has its connections closed.
In short, it should work for all connections that are attached to workspaces in the workspaces collection.
We use this code in a module of an App with about 30/40 forms, 5 classes and 4 modules. It successfully closes ALL of our database connections... but we don't use data controls etc. and mostly we don't explicitly create workspaces, we just use workspaces(0). In those cases where we do create a workspaces, we always go Workspaces.Append Ws etc.
.... Nothing wrong with your answer though.
I'd rather not make any of my databases public. I solved this problem in an application by placing the compact statement into the splash screen.
The splash screen fires before any other forms are loaded (ie., before any of the databases are opened)
There is a date checking routine to fire the compact process once per week.
If MonthView.DayOfWeek = mvwWednesday Then
Kill "c:\pos\posbak.mdb"
FileCopy "c:\pos\pos.mdb", "c:\pos\posbak.mdb"
CompactDatabase "C:\pos\pos.mdb", "c:\pos\pos1.mdb"
Kill "c:\pos\pos.mdb"
FileCopy "c:\pos\pos1.mdb", "c:\pos\pos.mdb"
Kill "c:\pos\pos1.mdb"
End If
I create a backup of my mdb file before doing the compact process (ya never know when you'll need a good backup file). It works. It's not clean, but it works
The splash screen fires before any other forms are loaded (ie., before any of the databases are opened)
There is a date checking routine to fire the compact process once per week.
If MonthView.DayOfWeek = mvwWednesday Then
Kill "c:\pos\posbak.mdb"
FileCopy "c:\pos\pos.mdb", "c:\pos\posbak.mdb"
CompactDatabase "C:\pos\pos.mdb", "c:\pos\pos1.mdb"
Kill "c:\pos\pos.mdb"
FileCopy "c:\pos\pos1.mdb", "c:\pos\pos.mdb"
Kill "c:\pos\pos1.mdb"
End If
I create a backup of my mdb file before doing the compact process (ya never know when you'll need a good backup file). It works. It's not clean, but it works
then close on exiting pgm
db.close
set db = nothing
rs.close
set rs = nothing