469,923 Members | 1,739 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,923 developers. It's quick & easy.

Compact on Open

How would I compact a database on Open using code? I presume I'd have to use
the Autoexec macro but what code do I put in the procedure called by
Autoexec?

Thanks,
Alistair
Nov 12 '05 #1
2 2289
It is not actually possible, since Jet's Compact functionality requires the
database to be closed (which is why even Access closes the current database
when you compact from the menu).
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.
"lestersal" <le*******@cox.net> wrote in message
news:c0KAb.32963$Gj2.23357@okepread01...
How would I compact a database on Open using code? I presume I'd have to use the Autoexec macro but what code do I put in the procedure called by
Autoexec?

Thanks,
Alistair

Nov 12 '05 #2
Here is a couple of snippets of code to compact the date portion of a
database (Linked tables)
Written a long time ago for Access 97 but works OK for A2K
Think you clicked a button on a form

Table Paths contains information on the folder for the various bits of the
database
Table Users was a list of people logged on to the database (irrelevant for a
single user)
It then creates a copy of the Data.Mdb called something like Data.Bak
(Just in case)
Then rename Data.Mdb to Data.MdbOld
Then Compact Data.MdbOld calling Data.Mdb the original name
Then relink the tables

You may be able to adapt this if using a front end back end situation

Phil

Sub CompactData_Click() ' Compact Database

Dim MyDb As Database
Dim PathSet As Recordset
Dim TDf As TableDef
Dim Pathname As String, FileName As String
Dim DataPath As String, OldDataPath As String, BakDataPath As String
Dim fs As Object

On Error GoTo CompactData_Err

' Check only 1 person (Current User) on System
If CheckUsers() > 1 Then Exit Sub ' Other users on system

ReturnValue = CloseForms()

Set MyDb = CurrentDb
Set PathSet = MyDb.OpenRecordset("Paths")
DataPath = PathSet!DataPath
PathSet.Close
Set PathSet = Nothing
OldDataPath = DataPath & "Old"
BakDataPath = Left$(DataPath, (Len(DataPath) - 3)) & "Bak"

ReturnValue = SysCmd(acSysCmdSetStatus, "Copying Data Files")
If Dir(DataPath) <> "" Then ' Make sure that the data file
exists
Set fs = CreateObject("Scripting.FileSystemObject")
fs.Copyfile DataPath, BakDataPath, True ' Copy Data to AMM
Services Data.Bak
If Dir(OldDataPath) <> "" Then
Kill OldDataPath ' Delete Old File
End If
fs.moveFile DataPath, OldDataPath ' Rename Data to AMM
Services Data.MdbOld
Else
MsgBox "Can't find the Data", vbCritical
Exit Sub
End If

ReturnValue = SysCmd(acSysCmdSetStatus, "Compacting Database")
DBEngine.CompactDatabase OldDataPath, DataPath

For Each TDf In MyDb.TableDefs ' Refresh the links
If Len(TDf.Connect) > 0 Then
If TDf.Connect > "" Then 'Drive letter for client
database
TDf.RefreshLink
End If
End If
Next TDf

MsgBox "Database Compacted Succesfully", vbInformation

CompactData_Exit:
ReturnValue = SysCmd(acSysCmdClearStatus)
Exit Sub

CompactData_Err:
MsgBox Err.Description
MsgBox "You can restore the data from 'AMM Services Data.Bak'",
vbInformation
Resume CompactData_Exit

End Sub

Function CloseForms()

Dim i As Integer

If Forms.Count > 1 Then ' Other Forms open
For i = 0 To Forms.Count - 1
If Forms(i).Name <> "Archive" And Forms(i).Name <> "Switchboard"
Then
DoCmd.Close acForm, Forms(i).Name
End If
Next
End If

End Function
Function CheckUsers() As Integer

Dim MyDb As Database
Dim UserSet As Recordset
Dim SQLStg As String

SQLStg = "SELECT Users.UserName, Users.LoggedOn "
SQLStg = SQLStg & "FROM Users;"

Set MyDb = CurrentDb
Set UserSet = MyDb.OpenRecordset(SQLStg)
UserSet.MoveLast
If UserSet.RecordCount > 1 Then
MsgBox "There are other users on the system. The must be logged off
before proceeding", vbInformation
CheckUsers = UserSet.RecordCount
End If

End Function
"lestersal" <le*******@cox.net> wrote in message
news:c0KAb.32963$Gj2.23357@okepread01...
How would I compact a database on Open using code? I presume I'd have to use the Autoexec macro but what code do I put in the procedure called by
Autoexec?

Thanks,
Alistair

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by robert demo via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.