run time error, user defined type is not defined

  • Thread starter Thread starter Jrough
  • Start date Start date
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")
 
It should work if you use

Dim objScript As Object

Then, you're doing what's known as Late Binding (which assures you won't
have problems with different users having different versions of the
Scripting library)
 
Jrough said:
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")
Click to expand...
 
Back
Top