script to compact database VBS

  • Thread starter Thread starter Janis
  • Start date Start date
J

Janis

I got this script from database journal. I put this script in a new
module. I have Access 2000 so I changed it to application9. I put in the
path.
I click run->sub/UserForm and it brings up an empty macro window? I don't
know why it doesn't compile and it tries to bring up a macro window. Is it
supposed to be attached to a form or to the database object ? I would like
it to run anytime the database closes from the close box or from the close
macros on the toolbar. There is a simple macro called ExitProgram with
the action "Quit" this is attached to a toolbar. I can't find where the
toolbar is created. It is not in form design. Can you get me started on
using this script? I don't know if I should attach this script to a macro in
the toolbar but probably not just have it run before the database closes. I
just wanted to see if the script would run or give me an error message. It
is not working because it is VBS not VBA? Is that the problem?
thanks,


Option Compare Database

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\SwimClubDev\acsc.mdb"
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.9")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting
database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************
Thanks,
 
It looks like a VBScript (Visual Basic Script). VBScripts are intended to be
run as stand alones or as a part of a web page. In this instance, the former.
VBS is a subset of VB in the same way that VBA is a subset of VB. There is
much that can be done via VBS. I should point out that there's a command line
switch that will open a designated Access mdb file and compact it which is a
viable alternative.

Open a text editor such as Notepad or WordPad. Copy the text to a new file
and save it as [fileName].vbs

Once saved, double click the .vbs file and you'll see that it'll run.

Here's some relavent information...
http://msdn.microsoft.com/en-us/library/9bbdkx3k(VS.85).aspx
 
This is for a user who is non-computer literate. So I don't think clicking
an VBS stand alone applet would do the trick. She has trouble opening
acccess. It has to be something she won't ever notice. That is why I wanted
it internal to the access db. By command line switch you mean the run
command window? So how does this switch work? Can it be automated to
compact this db every time it opens? Does this mean I have to write
something in VBA?

David H said:
It looks like a VBScript (Visual Basic Script). VBScripts are intended to be
run as stand alones or as a part of a web page. In this instance, the former.
VBS is a subset of VB in the same way that VBA is a subset of VB. There is
much that can be done via VBS. I should point out that there's a command line
switch that will open a designated Access mdb file and compact it which is a
viable alternative.

Open a text editor such as Notepad or WordPad. Copy the text to a new file
and save it as [fileName].vbs

Once saved, double click the .vbs file and you'll see that it'll run.

Here's some relavent information...
http://msdn.microsoft.com/en-us/library/9bbdkx3k(VS.85).aspx


Janis said:
I got this script from database journal. I put this script in a new
module. I have Access 2000 so I changed it to application9. I put in the
path.
I click run->sub/UserForm and it brings up an empty macro window? I don't
know why it doesn't compile and it tries to bring up a macro window. Is it
supposed to be attached to a form or to the database object ? I would like
it to run anytime the database closes from the close box or from the close
macros on the toolbar. There is a simple macro called ExitProgram with
the action "Quit" this is attached to a toolbar. I can't find where the
toolbar is created. It is not in form design. Can you get me started on
using this script? I don't know if I should attach this script to a macro in
the toolbar but probably not just have it run before the database closes. I
just wanted to see if the script would run or give me an error message. It
is not working because it is VBS not VBA? Is that the problem?
thanks,


Option Compare Database

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\SwimClubDev\acsc.mdb"
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.9")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting
database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************
Thanks,
Click to expand...
Click to expand...
 
VBScript or commandline, there's not that much of a difference on the user
end. I actually have a VBScript that the users execute to actually starts
Access and does some housecleaning prior to turning the control over to the
users - and they never know. The VBScript can be written, saved and then
emailed just like any other file - its actually how a good number of the
early viruses worked.

If you go with the command line, its just a matter of creating a short cut
and then adding the command line to the target property of the shortcut...

" C:\Program Files\Microsoft Office\Office\msaccess.exe C:\Documents and
Settings\dch3\Desktop\SQLImport.mdb /compact"

You may have to play with the syntax a bit as that's off the top of my head
and its been awhile since I've actually done it myself. The articles below
should help...

http://support.microsoft.com/default.aspx/kb/209207
http://office.microsoft.com/en-us/access/HA102355831033.aspx

Janis said:
This is for a user who is non-computer literate. So I don't think clicking
an VBS stand alone applet would do the trick. She has trouble opening
acccess. It has to be something she won't ever notice. That is why I wanted
it internal to the access db. By command line switch you mean the run
command window? So how does this switch work? Can it be automated to
compact this db every time it opens? Does this mean I have to write
something in VBA?

David H said:
It looks like a VBScript (Visual Basic Script). VBScripts are intended to be
run as stand alones or as a part of a web page. In this instance, the former.
VBS is a subset of VB in the same way that VBA is a subset of VB. There is
much that can be done via VBS. I should point out that there's a command line
switch that will open a designated Access mdb file and compact it which is a
viable alternative.

Open a text editor such as Notepad or WordPad. Copy the text to a new file
and save it as [fileName].vbs

Once saved, double click the .vbs file and you'll see that it'll run.

Here's some relavent information...
http://msdn.microsoft.com/en-us/library/9bbdkx3k(VS.85).aspx


Janis said:
I got this script from database journal. I put this script in a new
module. I have Access 2000 so I changed it to application9. I put in the
path.
I click run->sub/UserForm and it brings up an empty macro window? I don't
know why it doesn't compile and it tries to bring up a macro window. Is it
supposed to be attached to a form or to the database object ? I would like
it to run anytime the database closes from the close box or from the close
macros on the toolbar. There is a simple macro called ExitProgram with
the action "Quit" this is attached to a toolbar. I can't find where the
toolbar is created. It is not in form design. Can you get me started on
using this script? I don't know if I should attach this script to a macro in
the toolbar but probably not just have it run before the database closes. I
just wanted to see if the script would run or give me an error message. It
is not working because it is VBS not VBA? Is that the problem?
thanks,


Option Compare Database

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\SwimClubDev\acsc.mdb"
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.9")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting
database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
I ran the script like you suggested. I got the error "c:\documents and
settings\janis\desktop\compact.vbs
line 19
char 5
ActiveX component can't create object: 'Access.application.9 VBSScript
runtime error.
Isn't it better to do the compacting withing Access?
thanks,


David H said:
It looks like a VBScript (Visual Basic Script). VBScripts are intended to be
run as stand alones or as a part of a web page. In this instance, the former.
VBS is a subset of VB in the same way that VBA is a subset of VB. There is
much that can be done via VBS. I should point out that there's a command line
switch that will open a designated Access mdb file and compact it which is a
viable alternative.

Open a text editor such as Notepad or WordPad. Copy the text to a new file
and save it as [fileName].vbs

Once saved, double click the .vbs file and you'll see that it'll run.

Here's some relavent information...
http://msdn.microsoft.com/en-us/library/9bbdkx3k(VS.85).aspx


Janis said:
I got this script from database journal. I put this script in a new
module. I have Access 2000 so I changed it to application9. I put in the
path.
I click run->sub/UserForm and it brings up an empty macro window? I don't
know why it doesn't compile and it tries to bring up a macro window. Is it
supposed to be attached to a form or to the database object ? I would like
it to run anytime the database closes from the close box or from the close
macros on the toolbar. There is a simple macro called ExitProgram with
the action "Quit" this is attached to a toolbar. I can't find where the
toolbar is created. It is not in form design. Can you get me started on
using this script? I don't know if I should attach this script to a macro in
the toolbar but probably not just have it run before the database closes. I
just wanted to see if the script would run or give me an error message. It
is not working because it is VBS not VBA? Is that the problem?
thanks,


Option Compare Database

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\SwimClubDev\acsc.mdb"
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.9")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting
database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************
Thanks,
Click to expand...
Click to expand...
 
It honestly doesn't matter as its Access that does the compacting. The
command line just makes it a bit easier because it eliminates the potential
for errors, however the script has the added benefit of backing up the file
first before its compacted - something the command line can't do. The error
that you received is related to the script itself. When you're working with
VBScripts, you can't copy and paste from one environment to another given
that the script has to be tweeked. In this instance there are two SET
statements one if you're using Access '97 and another if you're using Access
2000. From there, you also have to change any relevant variables to point to
the file to be compacted as well as where you want the backup to be saved.

Janis said:
I ran the script like you suggested. I got the error "c:\documents and
settings\janis\desktop\compact.vbs
line 19
char 5
ActiveX component can't create object: 'Access.application.9 VBSScript
runtime error.
Isn't it better to do the compacting withing Access?
thanks,


David H said:
It looks like a VBScript (Visual Basic Script). VBScripts are intended to be
run as stand alones or as a part of a web page. In this instance, the former.
VBS is a subset of VB in the same way that VBA is a subset of VB. There is
much that can be done via VBS. I should point out that there's a command line
switch that will open a designated Access mdb file and compact it which is a
viable alternative.

Open a text editor such as Notepad or WordPad. Copy the text to a new file
and save it as [fileName].vbs

Once saved, double click the .vbs file and you'll see that it'll run.

Here's some relavent information...
http://msdn.microsoft.com/en-us/library/9bbdkx3k(VS.85).aspx


Janis said:
I got this script from database journal. I put this script in a new
module. I have Access 2000 so I changed it to application9. I put in the
path.
I click run->sub/UserForm and it brings up an empty macro window? I don't
know why it doesn't compile and it tries to bring up a macro window. Is it
supposed to be attached to a form or to the database object ? I would like
it to run anytime the database closes from the close box or from the close
macros on the toolbar. There is a simple macro called ExitProgram with
the action "Quit" this is attached to a toolbar. I can't find where the
toolbar is created. It is not in form design. Can you get me started on
using this script? I don't know if I should attach this script to a macro in
the toolbar but probably not just have it run before the database closes. I
just wanted to see if the script would run or give me an error message. It
is not working because it is VBS not VBA? Is that the problem?
thanks,


Option Compare Database

' ***************** BEGIN CODE HERE ' *****************
'
Dim objScript
Dim objAccess
Dim strPathToMDB
Dim strMsg

' ///////////// NOTE: User must edit variables in this section /////
'
' The following line of code is the only variable that need be edited
' You must provide a path to the Access MDB which will be compacted
'
strPathToMDB = "C:\SwimClubDev\acsc.mdb"
'
' ////////////////////////////////////////////////////////////////

' Set a name and path for a temporary mdb file
strTempDB = "C:\Comp0001.mdb"

' Create Access 97 Application Object
Set objAccess = CreateObject("Access.Application.9")

' For Access 2000, use Application.9
'Set objAccess = CreateObject("Access.Application.9")

' Perform the DB Compact into the temp mdb file
' (If there is a problem, then the original mdb is preserved)
objAccess.DBEngine.CompactDatabase strPathToMDB, strTempDB

If Err.Number > 0 Then
' There was an error. Inform the user and halt execution
strMsg = "The following error was encountered while compacting
database:"
strMsg = strMsg & vbCrLf & vbCrLf & Err.Description
Else
' Create File System Object to handle file manipulations
Set objScript = CreateObject("Scripting.FileSystemObject")

' Back up the original file as Filename.mdbz. In case of undetermined
' error, it can be recovered by simply removing the terminating "z".
objScript.CopyFile strPathToMDB, strPathToMDB & "z", True

' Copy the compacted mdb by into the original file name
objScript.CopyFile strTempDB, strPathToMDB, True

' We are finished with TempDB. Kill it.
objScript.DeleteFile strTempDB
End If

' Always remember to clean up after yourself
Set objAccess = Nothing
Set objScript = Nothing
'
' ****************** END CODE HERE ' ******************
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Hi Janis,

If you want to compact your database and not require your user to be
involved - on the main Access
menu select Tools/Options/General and check "Compact on Close". It will
compact everytime it closes.

Regards

Kevin
 
Probably not, but at least if you're running a script to do it, you can make
a copy before compacting. As well, you can compact the back-end, which
CompactOnClose cannot do.

Of course, there's no reason to compact every time you close the database...
 
Thanks a lot Douglas. I learned a little about vbscripting plus I think
anyone can figure out how to click an icon. Besides it would take me a day
or three to rewrite it in VBA :-)
 
You're welcome. However, I question your comment that it would take you "a
day or three to rewrite it in VBA". That code could be plunked into VBA with
no changes! (Of course, it should be changed ever so slightly, so that each
variable is dimmed as a specific type).
 
Well it works really well on the desktop. I could dim the path variables as
strings but I couldn't figure out where to put the module. It seems like
they have a close macro on a tool bar. I couldn't figure out where to attach
the module to.
thanks,
 
If you want to compact your database and not require your user to
be involved - on the main Access
menu select Tools/Options/General and check "Compact on Close". It
will compact everytime it closes.
Click to expand...

Terrible advice!

First off, no application should be unsplit, so the only time the
data would be compacted would be if the user opened the back end on
purpose, when they'd normally be using the front end. Front ends
never need to be compacted (except if they're improperly designed),
so COMPACT ON CLOSE is useless, as it will only ever run on the
front end, which shouldn't ever need to be compacted.

Secondly, in some circumstances, a corrupt database will not tell
you it's corrupt, but when you compact, you can lose data that was
still accessible before the compact operation. Since COMPACT ON
CLOSE does not make a backup, it's VERY, VERY DANGEROUS.

It's a feature that MS never should have implemented as it serves no
purpose at all in a proplerly implemented application, and is
downright dangerous in all other circumstances.
 
But is no worse than compacting by code on close - n'est pas?.
Click to expand...

Depends on whether or not you make a backup in your code (which
COMPACT ON CLOSE does not do) and whether or not you give your user
an option to skip the compact (something that should have been
implemented in COMPACT ON CLOSE).

So, yes, COMPACT ON CLOSE can be a much, much worse operation than
properly designed code that compacts on close.
 
Back
Top