Compacting on Size

[Guest]

Is it possible to compact a database from an open form based on the size of
that database?

I would like to be able to check the size of the database each time the main
form is opened. If that size is greater than 1.7 gig, I'd like the db to
automatically compact.

Possible? I am using Access 2003.

 

[Warren]

Hi GracieLou,

Shell "msaccess.exe. " & DB & " /repair", vbNormalFocus

where DB is my database location.

I use this to compact my back-end file.

-------------------------------------------------

DBEngine.CompactDatabase

is apparently the code to to compact the current database... (I have
never used it)

------------------------------------------------

FileLen(CurrentProject.FullName)

Gives you a db size thus:

If FileLen(CurrentProject.FullName) > YOURPRESETSIZE Then
' Your compact procedure
Else
Exit Sub
End If

----------------------------------------------

To display the db size on a form:

Have a textbox use:
="Database Size: " &
GetFormattedFileSize(FileLen(CurrentProject.FullName))


Where the "GetFormattedFileSize" function is:

Private Declare Function StrFormatByteSize Lib "shlwapi" _
Alias "StrFormatByteSizeA" (ByVal dw As Long, _
ByVal pszBuf As String, ByVal cchBuf As Long) As Long

Public Function GetFormattedFileSize(ByVal lSize As Long) As String
'-------------------------------------------------------------
'USAGE: GetFormattedFileSize(FileLen(CurrentProject.FullName))
' (for database size)
'-------------------------------------------------------------
Dim strOut As String
strOut = Space(64)
Call StrFormatByteSize(lSize, strOut, Len(strOut) - 1)
GetFormattedFileSize = Trim(strOut)
End Function

----------------------------------------------------

Sorry I'm a bit rushed; I am rushing. But I hope it helps!


Warren

 

[Guest]

Warren,

Thanks for the reply.

I have not tested this yet, but I have a question. Will this open the db or
will it be closed? I think I remember reading something about you could not
compact a db from code and have it open the db when done.

I know I could be all wet on this, but I really appreiate your response.

 

[Warren]

I think that may be correct. (I am no expert...)

I have an admin screen that has a button that compacts and repairs the
back-end of the database. This is my code (below). It tells the user
that everything will close, etc.


--------------------------------------------------------------------------------------------------
Private Sub cmdCompact_Click()
Dim DB As String

DB = """" & DLookup("[FileLocation]", "tbl_FileLocations",
"[Name]='Database'") & """"

Repsonse = MsgBox("Compacting and Reparing the Database will close
any open screens, run the Compact and Repair procedure and then exit
the Database. You will also be asked for the Database password." &
vbCrLf & vbCrLf & "Do you wish to continue?", vbYesNo + vbQuestion,
"Compact and Repair")
If Repsonse = vbNo Then Exit Sub

Do While Forms.Count > 0
DoCmd.Close acForm, Forms(0).Name, acSavePrompt
Loop

Do While Reports.Count > 0
DoCmd.Close acReport, Reports(0), acSavePrompt
Loop

MsgBox "This process may take several minutes. Do not close any
Microsoft Access windows during the Compact and Repair procedure.",
vbExclamation, "Warning"

Shell "msaccess.exe. " & DB & " /repair", vbNormalFocus
DoCmd.Quit acQuitPrompt
End Sub




Again, I am no expert but this works quite well in my opinion...