DB Grows, possible to auto compact DB?

[ADezii]

Hello All,

I was wondering if anyone had ever experienced this.

When ever I use my DB, it continues to get larger. Is there away to automatically compact the database upon closing or opening? Any help would be greatly apprecited.

If you need some more information, please let me know.

If all else fails, why not use a Batch File either as a Shortcut on the Desktop or as a Scheduled Task. Here are the contents of Compact.bat

@echo off
cls
cd\Program Files\Microsoft Office\OFFICE11
MSACCESS.EXE /compact C:\Test_Directory\Employees.mdb

[ChaseCox]

Is strFName = "NewText.vb s" or whatever your vbs file is named and are they both in the same folder?

They are both on the Desktop currently

[ChaseCox]

If all else fails, why not use a Batch File either as a Shortcut on the Desktop or as a Scheduled Task. Here are the contents of Compact.bat

@echo off
cls
cd\Program Files\Microsoft Office\OFFICE11
MSACCESS.EXE /compact C:\Test_Directory\Employees.mdb

Can you explain how to implement this tool Adezii? Thanks for the extra inpout

[Denburt]

O.K. i created a new database and tested it. I did need to make a couple of minor adjustments but it is working. You are using Access 97 so there may be an issue there but I don't think there should be.

I commented out the delete line (it can be removed):
The Runit line should be O.K.
strPath = "C:\Documen ts and Settings\denbur t\Desktop"
StrFName = "NewText.vb s"
RunIt strPath, StrFName
'DeleteIt StrFName

In the VBS script file make sure you adjust the following lines to suite your needs:

strTempDB = Path & "Comp0001.m db"
strDBName = Path & "MenuButtons.md b"

Also in the VBS script file I added:

CompactMe()

Right before the following line:
Sub CompactMe()

I hope this does the trick for you.

[Denburt]

LOL thanks Adezii as usual I have obviously made this more complicated than it needed to be.

[ChaseCox]

LOL thanks Adezii as usual I have obviously made this more complicated than it needed to be.

Is ADezii's solution easier? I can not get the module to compile still

[NeoPa]

Chase, do you know anything about :

1. Batch (Command) files?
2. The AT scheduler?

[ChaseCox]

Chase, do you know anything about :

1. Batch (Command) files?
2. The AT scheduler?

Batch Files. no

AT yes.

[Denburt]

Is ADezii's solution easier? I can not get the module to compile still

Still debugs on the same line?

[ChaseCox]

Still debugs on the same line?

Yes, I have tried commenting it out, but then more errors occur.

[Denburt]

O.K. reviewing previous posts.

Sub CompactSub()
'Define strPath and strFName and declare all variables
Dim strPath, strFName As String

strPath = "C:\Documents and Settings\laoxb\Desktop"
strFName = "Test.vbs"

RunIt strPath, strFName
DeleteIt strFName
DoEvents
DBEngine.Idle
Application.Quit
End Function

Remove the "\FalconAnalysi s.mdb" from your strPath This is how it should look.

[ChaseCox]

O.K. reviewing previous posts.

Sub CompactSub()
'Define strPath and strFName and declare all variables
Dim strPath, strFName As String

strPath = "C:\Documents and Settings\laoxb\Desktop"
strFName = "Test.vbs"

RunIt strPath, strFName
DeleteIt strFName
DoEvents
DBEngine.Idle
Application.Quit
End Function

Remove the "\FalconAnalysi s.mdb" from your strPath This is how it should look.

I am still getting the same error.

[Denburt]

Are you sure you have all the code in that module that you should have.

I noticed when I reposted I had that stupid delete statement in there again my apologies. If we can get the RunIt function to cooperate then the rest should be easy.

Sub CompactSub()
Dim strPath As String, StrFName As String
strPath = "C:\Documents and Settings\denburt\Desktop"
StrFName = "NewText.vbs"
RunIt strPath, StrFName
DoEvents
DBEngine.Idle
Application.Quit
End Sub

BTW Adezi's code is for the external file which we are not able to run until we get this RunIt issue worked out.

Is the error still the same?
byRef argument type mismatch

[ChaseCox]

Are you sure you have all the code in that module that you should have.

I noticed when I reposted I had that stupid delete statement in there again my apologies. If we can get the RunIt function to cooperate then the rest should be easy.

Sub CompactSub()
Dim strPath As String, StrFName As String
strPath = "C:\Documents and Settings\denburt\Desktop"
StrFName = "NewText.vbs"
RunIt strPath, StrFName
DoEvents
DBEngine.Idle
Application.Quit
End Sub

BTW Adezi's code is for the external file which we are not able to run until we get this RunIt issue worked out.

Is the error still the same?
byRef argument type mismatch

Yes it is the same error message

[ChaseCox]

I have been messing around with the Batch file idea and came up with this.

@echo off
cls
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" %1 /compact /repair "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb"

Which works, but it prompts me in the access window to say ok, I want to supress that message box, any ideas on this tanget?