Compact MDB

[KEVIN97810]

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.

[PC Datasheet]

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
re******@pcdata sheet.com
www.pcdatasheet.com
"KEVIN97810 " <ke********@aol .com> wrote in message
news:20******** *************** ****@mb-m05.aol.com...

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.

[Paradigm]

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.qui t statement.

Public Function AutoCompactCurr entProject()
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.Cur rentProject.Pat h
strProjectName = Application.Cur rentProject.Nam e
filespec = strProjectPath & "\" & strProjectName
Set fs = CreateObject("S cripting.FileSy stemObject")
Set f = fs.GetFile(file spec)
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.Set Option ("Auto Compact"), 1 'compact app
Else
Application.Set Option ("Auto Compact"), 0 'no don't compact app
End If
End Function

"PC Datasheet" <no****@nospam. spam> wrote in message
news:bc******** ***********@new sread1.news.pas .earthlink.net. ..

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
re******@pcdata sheet.com
www.pcdatasheet.com
"KEVIN97810 " <ke********@aol .com> wrote in message
news:20******** *************** ****@mb-m05.aol.com...

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.

[thomas.tracy]

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.

[Trevor Best]

KEVIN97810 wrote:

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.

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.ba t" For Output As #hFile
Print #hFile, "echo off"
Print #hFile, "cls"
Print #hFile, "echo compacting the Database.."
Print #hFile, "start /wait """ & SysCmd(acSysCmd AccessDir) &
"msaccess.e xe"" " & gdbCurrent.name & " /compact"
Print #hFile, ""
Print #hFile, "echo Re-Launching the Database.."
Print #hFile, "start """ & SysCmd(acSysCmd AccessDir) &
"msaccess.e xe"" " & 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.ba t", vbMaximizedFocu s)
Application.Qui t acQuitSaveNone

End Sub

--
This sig left intentionally blank

[John Mishefske]

Paradigm 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.qui t statement.

Public Function AutoCompactCurr entProject()
Dim fs, f, s, filespec
Dim strProjectPath As String, strProjectName As String
strProjectPath = Application.Cur rentProject.Pat h
strProjectName = Application.Cur rentProject.Nam e
filespec = strProjectPath & "\" & strProjectName
Set fs = CreateObject("S cripting.FileSy stemObject")
Set f = fs.GetFile(file spec)
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.Set Option ("Auto Compact"), 1 'compact app
Else
Application.Set Option ("Auto Compact"), 0 'no don't compact app
End If
End Function

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

s = CLng(FileLen(fi lespec) / 1000000) 'convert size

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

[Peter Russell]

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

Peter Russell

KEVIN97810 previously wrote:

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.