Compile error: variable not defined, ported from VBscript

[Janis]

I get an error on the WScript.Quit line. It can't compile. This is
presumably because I ported this script from VBscript to VBA. I want to
compact an Access db from with Access. How can I convert that line to VBA so
I can get this to compile?
tnx,

Public Sub compact()

Dim strMsg
Dim objScript As Object
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 = "XXXX"
dbe.DefaultPassword = "XXXXX"

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=XXXX"


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
.......................

 

[Douglas J. Steele]

Try replacing WScript.Quit with DoCmd.Quit

 

[Janis]

Thank you. It compiles further down in the script now almost to the end. I
think there is one more of those lines that needs to be translated to VBA.
It is on the CopyFile line. I get the error cannot compile: sub or function
not defined and it stops on the word Copy File.

tnx,

On Error GoTo 0

'perform db compact of backend mdb into a temp mdb first

'if there is a problem th
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)

DoCmd.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



'

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")



End Sub

 

[Douglas J. Steele]

objScript.CopyFile "Z:\SwimClub\temp_acsc_be.mdb",
"Z:\SwimClub\acsc_be.mdb", True

although realistically you don't need the overhead of objScript: VBA
includes the FileCopy statement:

If Len(Dir("Z:\SwimClub\acsc_be.mdb")) > 0 Then
Kill "Z:\SwimClub\acsc_be.mdb"
End If
FileCopy "Z:\SwimClub\temp_acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb"

(In case it's not apparent, my suggestion would be to remove all references
to objScript, not just that one)

 

[Janis]

Thanks very much. I have one small question left and its done. Way more work
than I thought. I tested it and it works. The temp file is being created and
deleted. The mdbz file however isn't updating. It keeps the same date
modified date and time. If I delete it then it creates the MDBZ otherwise
not. Is it possible to force it to write over the old file? tia





Option Compare Database
Option Explicit
Public Sub CompactBackEnd()

Dim strMsg
'Dim objScript As Object
'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 original mdb is saved
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)
DoCmd.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

FileCopy "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb" & "z"

' Copy the compacted mdb by into the original file name
FileCopy "Z:\SwimClub\temp_acsc_be.mdb", "Z:\SwimClub\acsc_be.mdb"

' We are finished with TempDB. Kill it.
'objscript.DeleteFile "Z:\SwimClub\temp_acsc_be.mdb"
If Len(Dir("Z:\SwimClub\temp_acsc_be.mdb")) > 0 Then

Kill "Z:\SwimClub\temp_acsc_be.mdb"
End If
Call MsgBox("compact successful")


End Sub

 

[Douglas J. Steele]

I thought FileCopy will overwrite, but what's wrong with just deleting the
file first if it already exists?

If Len(Dir("Z:\SwimClub\acsc_be.mdbz")) > 0 Then
Kill "Z:\SwimClub\acsc_be.mdbz"
End If

FileCopy "Z:\SwimClub\acsc_be.mdb", "Z:\SwimClub\acsc_be.mdbz"