Hello to all,
Assume my mdb name is Cust.MDB. I want to compact & repair
the same Cust.MDB when the user exit the program. Is there a way you can do it
in code behind the EXIT button. I am dumping data from SQL to temp table and
also delete from these tables in Cust.MDB. This would cause the MDB to get
big. So I just want some code to compact and repair as user Exit from the
access database.

thanks a bunch.

The better thing to do is to just use the Kill command to delete Cust.mdb
and then recreate Cust.mdb and its tables.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource (AT) pcdatasheet (DOT) com
www.pcdatasheet.com


"KEVIN97810" <kevin97810 (AT) aol (DOT) com> wrote

I use the following function just before quiting the application to test for
a file saize and set the autocompact on close to true if the file is greater
than a certain size. This means I do not have to compact it everytime it
closes but only when a certain size has been exceeded. Call it just before
your application.quit statement.

Public Function AutoCompactCurrentProject()
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.CurrentProject.Path
strProjectName = Application.CurrentProject.Name
filespec = strProjectPath & "\" & strProjectName
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(filespec)
s = CLng(f.Size / 1000000) 'convert size of app from bytes to Mb's
If s > 3 Then 'edit the 3 (Mb's) to the max size you want
to allow your app to grow.
Application.SetOption ("Auto Compact"), 1 'compact app
Else
Application.SetOption ("Auto Compact"), 0 'no don't compact app
End If
End Function

"PC Datasheet" <nospam (AT) nospam (DOT) spam> wrote

I have downloaded and use the compact addin from the Access MVP site.
Key challenge with this is the need to load the add in on any computer
that will use it, but it works very nicely from a single line calling
it from an exit button.

KEVIN97810 wrote:
watch line wrapping. This was to compact at any time and would restart
the db but can be modified.

Sub compactme()
Dim hFile As Integer
hFile = FreeFile()
Open Environ("TEMP") & "\repair.bat" For Output As #hFile
Print #hFile, "echo off"
Print #hFile, "cls"
Print #hFile, "echo compacting the Database.."
Print #hFile, "start /wait """ & SysCmd(acSysCmdAccessDir) &
"msaccess.exe"" " & gdbCurrent.name & " /compact"
Print #hFile, ""
Print #hFile, "echo Re-Launching the Database.."
Print #hFile, "start """ & SysCmd(acSysCmdAccessDir) &
"msaccess.exe"" " & gdbCurrent.name
Print #hFile, "del c:\temp\repair.bat"
Print #hFile, "this will never execute and DOS will complain about
""Batch File Missing"" but user shouldn't see it"
Close #hFile
Debug.Print Shell(Environ("TEMP") & "\repair.bat", vbMaximizedFocus)
Application.Quit acQuitSaveNone

End Sub

--
This sig left intentionally blank

Paradigm wrote:
Interesting idea. I'd suggest removing the use of the FileSystemObject
and using native VBA to get the file size:

s = CLng(FileLen(filespec) / 1000000) 'convert size

Thanks for the idea...
--
'---------------
'John Mishefske
'---------------

If you are using A2000 or later you can set the Compact On Close option in
Tools>Options.

Peter Russell

KEVIN97810 previously wrote: