469,292 Members | 1,310 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,292 developers. It's quick & easy.

DB Grows, possible to auto compact DB?

ChaseCox
294 100+
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.
Apr 3 '07 #1
53 4396
Denburt
1,356 Expert 1GB
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.
Apr 3 '07 #2
Denburt
1,356 Expert 1GB
You might check out the possibility of adding compact on the right click menu when your in explorer.

Right Click Compact menu
Apr 3 '07 #3
ChaseCox
294 100+
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
Apr 3 '07 #4
Denburt
1,356 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.CopyDatabaseFile strDbName, True, True
  2.     DBEngine.CompactDatabase strDbName, strTempDB
  3.  
Apr 3 '07 #5
ChaseCox
294 100+
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.
Apr 3 '07 #6
Denburt
1,356 Expert 1GB
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.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Private Declare Function apiShellExecute Lib "shell32.dll" _
  4.     Alias "ShellExecuteA" _
  5.     (ByVal hwnd As Long, _
  6.     ByVal lpOperation As String, _
  7.     ByVal lpFile As String, _
  8.     ByVal lpParameters As String, _
  9.     ByVal lpDirectory As String, _
  10.     ByVal nShowCmd As Long) _
  11.     As Long
  12. Public Const WIN_NORMAL = 1         'Open Normal
  13. Private Const ERROR_SUCCESS = 32&
  14. Private Const ERROR_NO_ASSOC = 31&
  15. Private Const ERROR_OUT_OF_MEM = 0&
  16. Private Const ERROR_FILE_NOT_FOUND = 2&
  17. Private Const ERROR_PATH_NOT_FOUND = 3&
  18. Private Const ERROR_BAD_FORMAT = 11&
  19.  
  20.  
  21. Sub CompactSub()
  22. 'Define strPath and strFName and declare all variables
  23.  
  24. RunIt strPath, strFName
  25. DeleteIt strFName
  26. DoEvents
  27. DBEngine.Idle
  28. Application.Quit
  29. End Function
  30. Sub RunIt(strPath As String, strFName As String)
  31. Dim varRet
  32. varRet = fHandleFile(strPath & strFName, WIN_NORMAL)
  33. End Sub
  34. Function DeleteIt(strFile)
  35. Dim FSO, strErr As String
  36. Set FSO = CreateObject("Scripting.FileSystemObject")
  37. strErr = FSO.DeleteFile(strFile)
  38. 'If strErr > 0 Then
  39. ' WS.PopUp strErr, 3
  40. 'End If
  41. Set FSO = Nothing
  42. End Function
  43.  
  44. Function fHandleFile(stFile As String, lShowHow As Long)
  45. Dim lRet As Long, varTaskID As Variant
  46. Dim stRet As String
  47.     'First try ShellExecute
  48.     lRet = apiShellExecute(hWndAccessApp, vbNullString, _
  49.             stFile, vbNullString, vbNullString, lShowHow)
  50.  
  51.     If lRet > ERROR_SUCCESS Then
  52.         stRet = vbNullString
  53.         lRet = -1
  54.     Else
  55.         Select Case lRet
  56.             Case ERROR_NO_ASSOC:
  57.                 'Try the OpenWith dialog
  58.                 varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
  59.                         & stFile, WIN_NORMAL)
  60.                 lRet = (varTaskID <> 0)
  61.             Case ERROR_OUT_OF_MEM:
  62.                 stRet = "Error: Out of Memory/Resources. Couldn't execute!"
  63.             Case ERROR_FILE_NOT_FOUND:
  64.                 stRet = "Error: File not found.  Couldn't Execute!"
  65.             Case ERROR_PATH_NOT_FOUND:
  66.                 stRet = "Error: Path not found. Couldn't Execute!"
  67.             Case ERROR_BAD_FORMAT:
  68.                 stRet = "Error:  Bad File Format. Couldn't Execute!"
  69.             Case Else:
  70.         End Select
  71.     End If
  72.     fHandleFile = lRet & _
  73.                 IIf(stRet = "", vbNullString, ", " & stRet)
  74. End Function
  75.  
Apr 3 '07 #7
ChaseCox
294 100+
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.
Apr 3 '07 #8
Denburt
1,356 Expert 1GB
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:

Expand|Select|Wrap|Line Numbers
  1. Sub CompactMe()
  2. on error resume next 
  3.     Set objAccess = WScript.CreateObject("Access.Application")
  4.     Set objScript= WScript.CreateObject("Scripting.FileSystemObject")
  5.     objScript.CopyFile strDBName , strDBName & "z", True
  6. If Err.Number <> 0 Then 
  7.     myD=Err.Number & "  " & Err.Description & vbcrlf & "Creating a copy of the Database: " & strDBName 
  8.     WS.Popup myD, 3
  9.     err.Clear
  10.     WScript.Quit()
  11. End If
  12.     objAccess.DbEngine.CompactDatabase strDBName ,strTempDB
  13. If Err.Number <> 0 Then 
  14.     myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
  15.     WS.Popup myD, 3
  16.     err.Clear
  17.     WScript.Quit()
  18. End If    
  19. If objScript.FileExists(strDBName) Then
  20.     objScript.DeleteFile strDBName
  21. End If 
  22.     objScript.CopyFile strTempDB, strDBName, True
  23. If Err.Number <> 0 Then 
  24.     myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
  25.     WS.Popup myD, 3
  26.   EmailErr(Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "New Database after Compacting:" & strTempDB & vbcrlf & "line 181 Compacting the database subroutine.")
  27.     err.Clear
  28.     WScript.Quit()
  29. End If    
  30.     objScript.DeleteFile strTempDB
  31.     Set objScript = Nothing
  32. If Err.Number <> 0 Then 
  33.     myD=Err.Number & "  " & Err.Description & vbcrlf & "Database Name:" & strDBName &  vbcrlf & "Temp Database for Compacting:" & strTempDB & vbcrlf & "line 174 Compacting the database subroutine."
  34.     WS.Popup myD, 3
  35.     WScript.Quit()
  36. End If
  37. myD="The second stage to this script is to make sure the tables are relinked!" & vbcrlf & vbcrlf & "Please be patient!"
  38. WS.Popup myD, 3
  39. dbs.close
  40. objAccess.quit 2
  41. set objAccess = Nothing  
  42. Set dbs = nothing
  43. Set wks = Nothing
  44. set dao=nothing
  45. If Err.Number <> 0 Then 
  46.     WScript.Quit()
  47. End If
  48. End Sub
  49.  
Apr 3 '07 #9
ChaseCox
294 100+
strTempDb, and the other names, could you give me a break down of what those should be? I would really appreciate it.
Apr 3 '07 #10
Denburt
1,356 Expert 1GB
strDBName ,strTempDB

strDBName is your database name
strTempDB merely a temporary db which will be deleted using the script.
Expand|Select|Wrap|Line Numbers
  1. Path = GetPath
  2. strTempDB = Path & "Comp0001.mde"
  3. strDBName = Path & "MyMDE.mde"
  4. Function GetPath
  5. ' Return path to the current script
  6. path = WScript.ScriptFullName  ' script file name
  7. GetPath = Left(path, InstrRev(path, "\"))
  8. End Function
  9.  
The above Function only works if it is in the same directory as the database.
Apr 3 '07 #11
ChaseCox
294 100+
My version of access does not recognize WSscript. and what is this vbcrlf Thanks again
Apr 3 '07 #12
Denburt
1,356 Expert 1GB
Sorry the following should be in the VBS script:

Expand|Select|Wrap|Line Numbers
  1. Path = GetPath
  2. 'Temporary db to be deleted once Compact is complete
  3. strTempDB = Path & "Comp0001.mde"
  4. 'Your .mdb or mde file name here
  5. strDBName = Path & "MyMDE.mde"
  6.  
  7. Function GetPath
  8. ' Return path to the current script
  9. path = WScript.ScriptFullName  ' script file name
  10. GetPath = Left(path, InstrRev(path, "\"))
  11. End Function
  12.  
Apr 3 '07 #13
ChaseCox
294 100+
I am not sure I know what that means.
Apr 3 '07 #14
Denburt
1,356 Expert 1GB
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
Apr 3 '07 #15
NeoPa
32,173 Expert Mod 16PB
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.
Apr 4 '07 #16
ChaseCox
294 100+
When I post the second item from your post:
Expand|Select|Wrap|Line Numbers
  1. Path = GetPath
  2. strTempDB = Path & "Comp0001.mde"
  3. strDBName = Path & "MyMDE.mde"
  4. Function GetPath()
  5. ' Return path to the current script
  6. Path = WScript.ScriptFullName  ' script file name
  7. GetPath = Left(Path, InstrRev(Path, "\"))
  8. End Function
  9.  
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?
Apr 5 '07 #17
ChaseCox
294 100+
I just now realized that I should paste
Expand|Select|Wrap|Line Numbers
  1. Path = GetPath
  2. 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?
Apr 5 '07 #18
Denburt
1,356 Expert 1GB
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.
Apr 5 '07 #19
ChaseCox
294 100+
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?
Apr 5 '07 #20
Denburt
1,356 Expert 1GB
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.
Apr 5 '07 #21
ChaseCox
294 100+
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.
Apr 5 '07 #22
Denburt
1,356 Expert 1GB
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"
Apr 5 '07 #23
ChaseCox
294 100+
Expand|Select|Wrap|Line Numbers
  1. strPath = Application.CurrentProject.Path
  2.  
Can you break this down for me. Currentproject is not an option that my version recognizes, nor is Path
Apr 5 '07 #24
Denburt
1,356 Expert 1GB
O.K. been a while since I was in 97.

strPath = "C:\YourFolderWhereDBResides"


:)
Apr 5 '07 #25
ChaseCox
294 100+
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
Apr 5 '07 #26
Denburt
1,356 Expert 1GB
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?
Apr 5 '07 #27
ChaseCox
294 100+
Yes for both.



Do you get a button to debug when you get that error? If so what line does it highlight?
Expand|Select|Wrap|Line Numbers
  1.  RunIt strPath, strFName  
where strPath is highlighted
Apr 5 '07 #28
ChaseCox
294 100+
Expand|Select|Wrap|Line Numbers
  1. Sub CompactSub()
  2. 'Define strPath and strFName and declare all variables
  3. Dim strPath, strFName As String
  4.  
  5. strPath = "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb"
  6. strFName = "Test.vbs"
  7.  
  8. RunIt strPath, strFName
  9. DeleteIt strFName
  10. DoEvents
  11. DBEngine.Idle
  12. Application.Quit
  13. End Function
  14.  
This is the block in question now.
Apr 5 '07 #29
Denburt
1,356 Expert 1GB
Is strFName = "NewText.vbs" or whatever your vbs file is named and are they both in the same folder?
Apr 5 '07 #30
ADezii
8,800 Expert 8TB
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
Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. cd\Program Files\Microsoft Office\OFFICE11
  4. MSACCESS.EXE /compact C:\Test_Directory\Employees.mdb
Apr 5 '07 #31
ChaseCox
294 100+
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
Apr 5 '07 #32
ChaseCox
294 100+
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
Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. cd\Program Files\Microsoft Office\OFFICE11
  4. MSACCESS.EXE /compact C:\Test_Directory\Employees.mdb
Can you explain how to implement this tool Adezii? Thanks for the extra inpout
Apr 5 '07 #33
Denburt
1,356 Expert 1GB
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.
Apr 5 '07 #34
Denburt
1,356 Expert 1GB
LOL thanks Adezii as usual I have obviously made this more complicated than it needed to be.
Apr 5 '07 #35
ChaseCox
294 100+
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
Apr 5 '07 #36
NeoPa
32,173 Expert Mod 16PB
Chase, do you know anything about :
  1. Batch (Command) files?
  2. The AT scheduler?
Apr 5 '07 #37
ChaseCox
294 100+
Chase, do you know anything about :
  1. Batch (Command) files?
  2. The AT scheduler?
Batch Files. no

AT yes.
Apr 5 '07 #38
Denburt
1,356 Expert 1GB
Is ADezii's solution easier? I can not get the module to compile still
Still debugs on the same line?
Apr 5 '07 #39
ChaseCox
294 100+
Still debugs on the same line?

Yes, I have tried commenting it out, but then more errors occur.
Apr 5 '07 #40
Denburt
1,356 Expert 1GB
O.K. reviewing previous posts.

Expand|Select|Wrap|Line Numbers
  1. Sub CompactSub()
  2. 'Define strPath and strFName and declare all variables
  3. Dim strPath, strFName As String
  4.  
  5. strPath = "C:\Documents and Settings\laoxb\Desktop"
  6. strFName = "Test.vbs"
  7.  
  8. RunIt strPath, strFName
  9. DeleteIt strFName
  10. DoEvents
  11. DBEngine.Idle
  12. Application.Quit
  13. End Function
Remove the "\FalconAnalysis.mdb" from your strPath This is how it should look.
Apr 5 '07 #41
ChaseCox
294 100+
O.K. reviewing previous posts.

Expand|Select|Wrap|Line Numbers
  1. Sub CompactSub()
  2. 'Define strPath and strFName and declare all variables
  3. Dim strPath, strFName As String
  4.  
  5. strPath = "C:\Documents and Settings\laoxb\Desktop"
  6. strFName = "Test.vbs"
  7.  
  8. RunIt strPath, strFName
  9. DeleteIt strFName
  10. DoEvents
  11. DBEngine.Idle
  12. Application.Quit
  13. End Function
Remove the "\FalconAnalysis.mdb" from your strPath This is how it should look.

I am still getting the same error.
Apr 5 '07 #42
Denburt
1,356 Expert 1GB
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.

Expand|Select|Wrap|Line Numbers
  1. Sub CompactSub()
  2. Dim strPath As String, StrFName As String
  3. strPath = "C:\Documents and Settings\denburt\Desktop"
  4. StrFName = "NewText.vbs"
  5. RunIt strPath, StrFName
  6. DoEvents
  7. DBEngine.Idle
  8. Application.Quit
  9. End Sub
  10.  
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
Apr 5 '07 #43
ChaseCox
294 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Sub CompactSub()
  2. Dim strPath As String, StrFName As String
  3. strPath = "C:\Documents and Settings\denburt\Desktop"
  4. StrFName = "NewText.vbs"
  5. RunIt strPath, StrFName
  6. DoEvents
  7. DBEngine.Idle
  8. Application.Quit
  9. End Sub
  10.  
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
Apr 5 '07 #44
ChaseCox
294 100+
I have been messing around with the Batch file idea and came up with this.

Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" %1 /compact /repair "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb" 
  4.  
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?
Apr 5 '07 #45
ADezii
8,800 Expert 8TB
I have been messing around with the Batch file idea and came up with this.

Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" %1 /compact /repair "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb" 
  4.  
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)?
Apr 6 '07 #46
NeoPa
32,173 Expert Mod 16PB
I have been messing around with the Batch file idea and came up with this.

Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" %1 /compact /repair "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb" 
  4.  
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 :(
Apr 10 '07 #47
NeoPa
32,173 Expert Mod 16PB
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.
Apr 10 '07 #48
NeoPa
32,173 Expert Mod 16PB
I have been messing around with the Batch file idea and came up with this.

Expand|Select|Wrap|Line Numbers
  1. @echo off
  2. cls
  3. "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" %1 /compact /repair "C:\Documents and Settings\laoxb\Desktop\FalconAnalysis.mdb" 
  4.  
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.
Apr 10 '07 #49
ChaseCox
294 100+
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.
Apr 10 '07 #50

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Charles McCaffery | last post: by
3 posts views Thread by trueblue7 | last post: by
20 posts views Thread by deepak | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.