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]

In the Access Database window go to the tools menu then Options Click the general tab and click on Compact on Close. I can also show you how it can be done in code if you would like.

[Denburt]

You might check out the possibility of adding compact on the right click menu when your in explorer.

Right Click Compact menu

[ChaseCox]

Thanks for the quick response. Would you mind showing me how to do this with code. I can not seem to find that option in my options tab selections. I am using access 97

[Denburt]

Well i'll be i have a whole slew of DB's here that i am managing and I thought for sure it was in one of them evidently not. must be in some of my older work. I did find it in VBS but that won't help in VBA. Anyhow here is a snippet I am sure you can run with this.

    DoCmd.CopyDatabaseFile strDbName, True, True
    DBEngine.CompactDatabase strDbName, strTempDB
 

[ChaseCox]

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.

[Denburt]

O.K. I use VBA to create a VBS script and call it for updating my clients Front Ends the following code should help you accomplish what you need. Note this was stripped out of my DB so some of it may not be needed.

Option Compare Database
Option Explicit
Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long
Public Const WIN_NORMAL = 1         'Open Normal
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&
 
 
Sub CompactSub()
'Define strPath and strFName and declare all variables
 
RunIt strPath, strFName
DeleteIt strFName
DoEvents
DBEngine.Idle
Application.Quit
End Function
Sub RunIt(strPath As String, strFName As String)
Dim varRet
varRet = fHandleFile(strPath & strFName, WIN_NORMAL)
End Sub
Function DeleteIt(strFile)
Dim FSO, strErr As String
Set FSO = CreateObject("Scripting.FileSystemObject")
strErr = FSO.DeleteFile(strFile)
'If strErr > 0 Then
' WS.PopUp strErr, 3
'End If
Set FSO = Nothing
End Function
 
Function fHandleFile(stFile As String, lShowHow As Long)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
 
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function
 

[ChaseCox]

Thanks I will look at this and see if I can not get it to work for me. If I do, I will post what I used.

[Denburt]

VBS Code again this was stripped out of a bunch of other code and other code was stripped from this so this is definately not a copy paste solution but should get you headed in the right direction. Remember though VBS does not handle errors well:

Sub CompactMe()
on error resume next 
    Set objAccess = WScript.CreateObject("Access.Application")
    Set objScript= WScript.CreateObject("Scripting.FileSystemObject")
    objScript.CopyFile strDBName , strDBName & "z", True
If Err.Number <> 0 Then 
    myD=Err.Number & "  " & Err.Description & vbcrlf & "Creating a copy of the Database: " & strDBName 
    WS.Popup myD, 3
    err.Clear
    WScript.Quit()
End If
    objAccess.DbEngine.CompactDatabase strDBName ,strTempDB
If Err.Number <> 0 Then 
    myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
    WS.Popup myD, 3
    err.Clear
    WScript.Quit()
End If    
If objScript.FileExists(strDBName) Then
    objScript.DeleteFile strDBName
End If 
    objScript.CopyFile strTempDB, strDBName, True
If Err.Number <> 0 Then 
    myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
    WS.Popup myD, 3
  EmailErr(Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "New Database after Compacting:" & strTempDB & vbcrlf & "line 181 Compacting the database subroutine.")
    err.Clear
    WScript.Quit()
End If    
    objScript.DeleteFile strTempDB
    Set objScript = Nothing
If Err.Number <> 0 Then 
    myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
    WS.Popup myD, 3
    WScript.Quit()
End If
myD="The second stage to this script is to make sure the tables are relinked!" & vbcrlf & vbcrlf & "Please be patient!"
WS.Popup myD, 3
dbs.close
objAccess.quit 2
set objAccess = Nothing  
Set dbs = nothing
Set wks = Nothing
set dao=nothing
If Err.Number <> 0 Then 
    WScript.Quit()
End If
End Sub
 

[ChaseCox]

strTempDb, and the other names, could you give me a break down of what those should be? I would really appreciate it.

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

[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.CurrentProject.Path
strFname = "YourVBSFileName.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:\YourFolderWhereDBResides"


:)

[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.vbs" or whatever your vbs file is named and are they both in the same folder?

[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.vbs" 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:\Documents and Settings\denburt\Desktop"
StrFName = "NewText.vbs"
RunIt strPath, StrFName
'DeleteIt StrFName

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

strTempDB = Path & "Comp0001.mdb"
strDBName = Path & "MenuButtons.mdb"

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 "\FalconAnalysis.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 "\FalconAnalysis.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?

[ADezii]

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?

I cannot understand why you are getting a prompt in the Access Window, the Batch File works fine on my System. What exactly does the prompt say and what Version of Access are you working with? What is the purpose of the Replaceable Parameter (%1)?

[NeoPa]

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?

You could try using the /nostartup parameter. Otherwise I'm not sure there is an option to suppress :(

[NeoPa]

I cannot understand why you are getting a prompt in the Access Window, the Batch File works fine on my System. What exactly does the prompt say and what Version of Access are you working with? What is the purpose of the Replaceable Parameter (%1)?

In Batch (.Bat) or Command (.Cmd) files, %1 represents the first parameter passed to the Batch/Command file.

[NeoPa]

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?

Chase,
You need to supply the name (of your Access db file) in the %1 variable or drop that and put the full path in its place.
The /compact & /repair parameters work on the preceding filename.

[ChaseCox]

Chase,
You need to supply the name (of your Access db file) in the %1 variable or drop that and put the full path in its place.
The /compact & /repair parameters work on the preceding filename.

Thanks, I got it working the other day.