J
Jrough
This was a port from vbscript to VBA. I need to dim the variables in order
to use VBA.
I dimmed the objScript as Scripting.FileSystemObject and
now I get a runtime error. It says "user defined type not defined" when I
try to compile.
In references I have a reference to
ActiveX Data Objects 2.1 Library. Do I need to give strMsg or dbEngine an
object type?
-----
Option Compare Database
Option Explicit
Public Sub compact()
Dim strMsg
Dim objScript As Scripting.FileSystemObject
Dim dbe As DBEngine
Set dbe = CreateObject("dao.dbengine.36")
'path is to System.mdb because it is a split database
dbe.SystemDB = "Z:\SwimClub\System.mdw"
dbe.DefaultUser = "Brian"
dbe.DefaultPassword = "Kiyote#3"
On Error GoTo 0
'perform db compact of backend mdb into a temp mdb first
'if there is a problem the origial mdb is preserved
dbe.CompactDatabase "Z:\SwimClub\acsc_be.mdb",
"Z:\SwimClub\temp_acsc_be.mdb", , , ";pwd=Kiyote#3"
If (Err.Number <> 0) Then
On Error GoTo 0
' 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
Call MsgBox(strMsg)
WScript.Quit
End If
On Error GoTo 0
' 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 "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" &
"z", True
' Copy the compacted mdb by into the original file name
objScript.CopyFile "Z:\SwimClub\temp_acsc_be.mdb",
"Z:\SwimClub\acsc_be.mdb", True
' We are finished with TempDB. Kill it.
'objscript.DeleteFile "Z:\SwimClub\temp_acsc_be.mdb"
Call MsgBox("compact successful")
to use VBA.
I dimmed the objScript as Scripting.FileSystemObject and
now I get a runtime error. It says "user defined type not defined" when I
try to compile.
In references I have a reference to
ActiveX Data Objects 2.1 Library. Do I need to give strMsg or dbEngine an
object type?
-----
Option Compare Database
Option Explicit
Public Sub compact()
Dim strMsg
Dim objScript As Scripting.FileSystemObject
Dim dbe As DBEngine
Set dbe = CreateObject("dao.dbengine.36")
'path is to System.mdb because it is a split database
dbe.SystemDB = "Z:\SwimClub\System.mdw"
dbe.DefaultUser = "Brian"
dbe.DefaultPassword = "Kiyote#3"
On Error GoTo 0
'perform db compact of backend mdb into a temp mdb first
'if there is a problem the origial mdb is preserved
dbe.CompactDatabase "Z:\SwimClub\acsc_be.mdb",
"Z:\SwimClub\temp_acsc_be.mdb", , , ";pwd=Kiyote#3"
If (Err.Number <> 0) Then
On Error GoTo 0
' 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
Call MsgBox(strMsg)
WScript.Quit
End If
On Error GoTo 0
' 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 "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" &
"z", True
' Copy the compacted mdb by into the original file name
objScript.CopyFile "Z:\SwimClub\temp_acsc_be.mdb",
"Z:\SwimClub\acsc_be.mdb", True
' We are finished with TempDB. Kill it.
'objscript.DeleteFile "Z:\SwimClub\temp_acsc_be.mdb"
Call MsgBox("compact successful")