B
Bart Holmes
Guest
Dear Access-Experts,
I am facing the following problem:
I have an access database (97), which links into several sub-databases. By starting an event procedure a main-table is created, containing data from the sub-databases. This process takes a while (20min) and it is necessary to compact the database at least once a week.
From a web-site I have found the code at the end of this email. This is a script to compact the database (saved as vbs) and runs fine. How can I add the line, which tells the system to execute the event procedure (merging sub-database data), before compacting the database?
The solution can be a vbs file or actually a VBA Module within MS-Access (even if I have to create a separate database).
Sorry to bother you with this question, however I am not an Microsoft Access expert.
Your help is greatly appreciated.
Bart
' ***************** 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:\*.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.8")
' 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, 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 ' ******************
I am facing the following problem:
I have an access database (97), which links into several sub-databases. By starting an event procedure a main-table is created, containing data from the sub-databases. This process takes a while (20min) and it is necessary to compact the database at least once a week.
From a web-site I have found the code at the end of this email. This is a script to compact the database (saved as vbs) and runs fine. How can I add the line, which tells the system to execute the event procedure (merging sub-database data), before compacting the database?
The solution can be a vbs file or actually a VBA Module within MS-Access (even if I have to create a separate database).
Sorry to bother you with this question, however I am not an Microsoft Access expert.
Your help is greatly appreciated.
Bart
' ***************** 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:\*.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.8")
' 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, 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 ' ******************
