DB Grows, possible to auto compact DB?

[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?

[Denburt]

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

Placing it in the database instead of the VBS file and that will happen. :) please see my previous post.

[ChaseCox]

Placing it in the database instead of the VBS file and that will happen. :) please see my previous post.

Dang, I am sorry about that. So maybe I will post some questions I have now.

When I compile the module it has a problem with this line

RunIt strPath, strFName

Does i matter what I call the .vbs file, I am sure it does. But I can not figure out where the module would call it.

How do I get this to run, I am guessing I will make it a on close event, but any input on the code to use would be awesome.

Thanks Again for the help.

[Denburt]

You can call the VBS file anything you want just make sure you establish this in the variable in your database.


'Define strPath and strFName and declare all variables

strPath = Application.Cur rentProject.Pat h
strFname = "YourVBSFileNam e.vbs"

[ChaseCox]

strPath = Application.CurrentProject.Path

Can you break this down for me. Currentproject is not an option that my version recognizes, nor is Path

[Denburt]

O.K. been a while since I was in 97.

strPath = "C:\YourFolderW hereDBResides"


:)

[ChaseCox]

Thanks,

how should the strPath amd strFName be declared as String or what?

I am now getting an error that reads,

byRef argument type mismatch any thoughts

[Denburt]

how should the strPath amd strFName be declared as String or what?

Yes for both.

I am now getting an error that reads,
byRef argument type mismatch any thoughts

Do you get a button to debug when you get that error? If so what line does it highlight?

[ChaseCox]

Yes for both.



Do you get a button to debug when you get that error? If so what line does it highlight?

 RunIt strPath, strFName

where strPath is highlighted

[ChaseCox]

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

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

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

This is the block in question now.

[Denburt]

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