By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Compact MDB

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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******@pcdatasheet.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.

Nov 13 '05 #2

P: n/a
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" <no****@nospam.spam> wrote in message
news:bc*******************@newsread1.news.pas.eart hlink.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******@pcdatasheet.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.


Nov 13 '05 #3

P: n/a
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.

Nov 13 '05 #4

P: n/a
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.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
Nov 13 '05 #5

P: n/a
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.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


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
'---------------
Nov 13 '05 #6

P: n/a
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.


Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.