babban
asked on
Compact database Access97
Is it possible to do a "Compact databse" whith
the VBA code?
So my project comapct itself ex. every month?
the VBA code?
So my project comapct itself ex. every month?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
u should know that u can't compact the database that u'r currently using.
Unless of course u use the sendkeys command to call up the toolbar Database Utilites and compact database. Then there's that piece of coding missing to rename the compacted database back to the orginal...
Unless of course u use the sendkeys command to call up the toolbar Database Utilites and compact database. Then there's that piece of coding missing to rename the compacted database back to the orginal...
ASKER
Hm?
dapperry says it could go and load_o_crap says it DON'T??????
dapperry says it could go and load_o_crap says it DON'T??????
No, I actually didn't say you could do that with :
DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"
That is only from an outside database. As far as renaming, I figured you probably knew that much, but for yucks..
DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"
Kill "Source.mdb"
Name "Dest.mdb" As "Source.mdb"
But if you need to do it from within the database, by all means use SendKeys as load_o_crap suggested.
:) D Perry
DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"
That is only from an outside database. As far as renaming, I figured you probably knew that much, but for yucks..
DBEngine.CompactDatabase "Source.mdb", "Dest.mdb"
Kill "Source.mdb"
Name "Dest.mdb" As "Source.mdb"
But if you need to do it from within the database, by all means use SendKeys as load_o_crap suggested.
:) D Perry
Below is a kb artical to use the method dapperry is talking about.
Article ID: Q158937
************************** ********** ********** ********** ********** ********** ********** ********
The following example uses a Visual Basic procedure to compact one or more
databases, and then to close Microsoft Access when it is finished. You
cannot compact the database that is running the procedure, nor can you
compact any database that you cannot open exclusively. You must have read
and write permissions for the folder where the database you are compacting
resides, and you need enough disk space in that folder to store the
original and the compacted copies of the database.
1. Create a new blank database called Compact.mdb.
2. Create the following new table in Design view:
Table: DBNames
-------------------------- ---------- ---------- ------
Field Name: DBID
Data Type: AutoNumber (or Counter in version 2.0)
Field Name: DBFolder
Data Type: Text
Field Size: 255
Field Name: DBName
Data Type: Text
Field Size 255
Table Properties: DBNames
-------------------------
PrimaryKey: DBID
3. Save the table as DBNames and close it.
4. Create a new blank form and set the following properties:
Caption: Compact Databases
Default View: Single Form
Scrollbars: Neither
RecordSelectors: No
NavigationButtons: No
OnTimer: [Event Procedure]
TimerInterval: 60000
5. Click the Build button next to the OnTimer property of the form and
type the following procedure:
In Microsoft Access 7.0 and 97
-------------------------- ----
Private Sub Form_Timer()
'========================= ========== ========== ========== ========== =
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins compacting all databases in the DBNames table.
'========================= ========== ========== ========== ========== =
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 AM"
' If StartTime is now, open the DBNames table and start compacting
If Format(Now(), "medium time") = Format(StartTime, _
"medium time") Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames" )
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.
' This example uses the old name plus the current date.
NewDbName = Left(DbName, Len(DbName) - 4)
NewDbName = NewDbName & " " & Format(Date, "MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
DoCmd.Quit acSaveYes
End If
End Sub
In Microsoft Access 2.0
-----------------------
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code in Access Basic.
Private Sub Form_Timer()
'========================= ========== ========== ========== ========== =
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins compacting all databases in the DBNames table.
'========================= ========== ========== ========== ========== =
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 AM"
' If StartTime is now, open the DBNames table and start compacting.
If Format(Now(), "medium time") = Format(StartTime, _
"medium time") Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames" )
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.
' This example uses the DBID plus the current date, which
' falls within DOS 8.3 file name limits for DBID = 1 to 99.
NewDBName = RS("DBFolder") & "\" & RS("DBID") & Format(Date, _
"MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access.
DoCmd Close a_Form, "CompactDB"
DoCmd Quit a_Save
End If
End Sub
6. Save the form as CompactDB and close it.
7. Create a new macro with the following action:
Action
--------
OpenForm
Action Arguments
--------------------
Form Name: CompactDB
View: Form
Data Mode: Read Only
Window Mode: Normal
8. Save the macro as AutoExec and close it.
9. Open the DBNames table and add a record for each database you want to
compact. Type the full path to the database in the DBFolder field, and
the name of the database itself in the DBName field. For example:
DBID DBFolder DBName
-------------------------- ---------- ---------- -----
1 C:\MSOffice\Access\Samples Northwind.mdb
2 \\Servername\Access\Sampap ps Nwind.mdb
10. Close the database, and then reopen it any time before compacting is
scheduled to start. The AutoExec macro will automatically open the
CompactDB form. Leave Microsoft Access running with this form open.
At the specified time, compacting begins and when the last database is
done, Microsoft Access closes.
Copyright (c) Microsoft Corporation. All rights reserved.
************************** ********** ********** ********** ********** ********** ********** ********** *****
Wayne
Article ID: Q158937
**************************
The following example uses a Visual Basic procedure to compact one or more
databases, and then to close Microsoft Access when it is finished. You
cannot compact the database that is running the procedure, nor can you
compact any database that you cannot open exclusively. You must have read
and write permissions for the folder where the database you are compacting
resides, and you need enough disk space in that folder to store the
original and the compacted copies of the database.
1. Create a new blank database called Compact.mdb.
2. Create the following new table in Design view:
Table: DBNames
--------------------------
Field Name: DBID
Data Type: AutoNumber (or Counter in version 2.0)
Field Name: DBFolder
Data Type: Text
Field Size: 255
Field Name: DBName
Data Type: Text
Field Size 255
Table Properties: DBNames
-------------------------
PrimaryKey: DBID
3. Save the table as DBNames and close it.
4. Create a new blank form and set the following properties:
Caption: Compact Databases
Default View: Single Form
Scrollbars: Neither
RecordSelectors: No
NavigationButtons: No
OnTimer: [Event Procedure]
TimerInterval: 60000
5. Click the Build button next to the OnTimer property of the form and
type the following procedure:
In Microsoft Access 7.0 and 97
--------------------------
Private Sub Form_Timer()
'=========================
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins compacting all databases in the DBNames table.
'=========================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 AM"
' If StartTime is now, open the DBNames table and start compacting
If Format(Now(), "medium time") = Format(StartTime, _
"medium time") Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames"
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.
' This example uses the old name plus the current date.
NewDbName = Left(DbName, Len(DbName) - 4)
NewDbName = NewDbName & " " & Format(Date, "MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access
DoCmd.Close acForm, "CompactDB", acSaveYes
DoCmd.Quit acSaveYes
End If
End Sub
In Microsoft Access 2.0
-----------------------
NOTE: In the following sample code, an underscore (_) at the end of a
line is used as a line-continuation character. Remove the underscore
from the end of the line when re-creating this code in Access Basic.
Private Sub Form_Timer()
'=========================
'The Timer event runs this code every minute. It compares your
'system time with the StartTime variable. When they match, it
'begins compacting all databases in the DBNames table.
'=========================
Dim StartTime As String
' Set this variable for the time you want compacting to begin.
StartTime = "12:00 AM"
' If StartTime is now, open the DBNames table and start compacting.
If Format(Now(), "medium time") = Format(StartTime, _
"medium time") Then
Dim RS As Recordset, DB As DATABASE
Dim NewDBName As String, DBName As String
Set DB = CurrentDb()
Set RS = DB.OpenRecordset("DBNames"
On Error Resume Next
RS.MoveFirst
Do Until RS.EOF
DBName = RS("DBFolder") & "\" & RS("DBName")
' Create a new name for the compacted database.
' This example uses the DBID plus the current date, which
' falls within DOS 8.3 file name limits for DBID = 1 to 99.
NewDBName = RS("DBFolder") & "\" & RS("DBID") & Format(Date, _
"MMDDYY") & ".mdb"
DBEngine.CompactDatabase DBName, NewDBName
RS.MoveNext
Loop
' Close the form, and then close Microsoft Access.
DoCmd Close a_Form, "CompactDB"
DoCmd Quit a_Save
End If
End Sub
6. Save the form as CompactDB and close it.
7. Create a new macro with the following action:
Action
--------
OpenForm
Action Arguments
--------------------
Form Name: CompactDB
View: Form
Data Mode: Read Only
Window Mode: Normal
8. Save the macro as AutoExec and close it.
9. Open the DBNames table and add a record for each database you want to
compact. Type the full path to the database in the DBFolder field, and
the name of the database itself in the DBName field. For example:
DBID DBFolder DBName
--------------------------
1 C:\MSOffice\Access\Samples
2 \\Servername\Access\Sampap
10. Close the database, and then reopen it any time before compacting is
scheduled to start. The AutoExec macro will automatically open the
CompactDB form. Leave Microsoft Access running with this form open.
At the specified time, compacting begins and when the last database is
done, Microsoft Access closes.
Copyright (c) Microsoft Corporation. All rights reserved.
**************************
Wayne
ASKER
OK,thanks.
ASKER
Adjusted points to 8
ASKER
Adjusted points to 10
:) D Perry