DB Grows, possible to auto compact DB?

[ChaseCox]

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.

[Denburt]

strDBName ,strTempDB

strDBName is your database name
strTempDB merely a temporary db which will be deleted using the script.

Path = GetPath
strTempDB = Path & "Comp0001.mde"
strDBName = Path & "MyMDE.mde"
Function GetPath
' Return path to the current script
path = WScript.ScriptFullName  ' script file name
GetPath = Left(path, InstrRev(path, "\"))
End Function
 

The above Function only works if it is in the same directory as the database.

[ChaseCox]

My version of access does not recognize WSscript. and what is this vbcrlf Thanks again

[Denburt]

Sorry the following should be in the VBS script:

Path = GetPath
'Temporary db to be deleted once Compact is complete
strTempDB = Path & "Comp0001.mde"
'Your .mdb or mde file name here
strDBName = Path & "MyMDE.mde"
 
Function GetPath
' Return path to the current script
path = WScript.ScriptFullName  ' script file name
GetPath = Left(path, InstrRev(path, "\"))
End Function
 

[ChaseCox]

I am not sure I know what that means.

[Denburt]

The following should be placed in a module in your DataBase:
http://www.thescripts.com/forum/post2482491-7.html


I use notepad for all my VBS stuff, When you save the file make sure the extension is somefile.vbs

Paste in the following and make your adjustments.
This should be at the top of the VBS script:
http://www.thescripts.com/forum/post2482534-11.html

Then this in the VBS script:

http://www.thescripts.com/forum/post2482509-9.html

[NeoPa]

I tried this snippet before. But it can only be used on a DB you are not currently in. I need someway to compact on closing. Any thoughts on this? I will keep looking for compact on close.

Compact on Close was introduced in the next version (2K) :(
vbCrLf is a predefined string within VBA which is just a Carriage-Return / Line-Feed sequence.
As denburt says, the ability to Compact your current database depends on getting out of it first - hence his idea to use VBScript.

[ChaseCox]

When I post the second item from your post:

Path = GetPath
strTempDB = Path & "Comp0001.mde"
strDBName = Path & "MyMDE.mde"
Function GetPath()
' Return path to the current script
Path = WScript.ScriptFullName  ' script file name
GetPath = Left(Path, InstrRev(Path, "\"))
End Function
 

I get an error when I compile that says invalid outside procedure. Where should I put this exactly, does it matter what I call the module?

Also the .mde, is that the same as .mdb. which should I use?

[ChaseCox]

I just now realized that I should paste

Path = GetPath
strTempDB = Path & "Comp0001.mde" ... 

in a seperate script file, because that is what you said to do, and I just did not listen, oops. Sorry. Does it matter what I call it?

Also when I compile my Form code, it does not recognize wscript. any thoughts?

[Denburt]

That snippet of code should be in your VBS script not the database. Make sure the database and script are in the same location or that function will not work as expected.

See this post

Your database can be either a mdb or mde. When you are in an MDB you can create an MDE which is simply creating a copy of your database that is completely compiled a little quicker and your users can't go into the code.

[ChaseCox]

Your database can be either a mdb or mde. When you are in an MDB you can create an MDE which is simply creating a copy of your database that is completely compiled a little quicker and your users can't go into the code.

Ok thanks. Any ideas about WScript?