ActiveCompanionSet xtras for Macromedia Director

Compact .mdb with VbScriptXtra

Sometimes Jet (Microsoft Access) databases need to be compacted to decrease database size. There is a library called Microsoft Jet and Replication Objects that provides JetEngine object. One of its methods allows compacting/converting existing database into another file. It also allows setting or changing Jet database password.

At first create an instance of a JetEngine object:
vb = xtra( "VbScriptXtra" )
-- Creating a new instance of JetEngine
jet = vb.CreateObject( "JRO.JetEngine" )

Then you have to know the connection string for your existing database. Note: the database should not be opened by anyone else during save and compact procedure. Normally it is something like:
strSourceCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb"

If you set the Jet password for your database, it looks like:
strSourceCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB.mdb; Jet OLEDB:Database Password=PasswordHere"

Then you have to build a connection string for the new file that will be created by this operation.
strDestCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB2.mdb"

You may specify new Jet database password or convert it to another engine type.
strDestCnn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\Temp\DB2.mdb; Jet OLEDB:New Database Password=NewPasswordHere; Jet OLEDB:Engine Type=5"

To get more details about Jet properties and settings visit MSDN.
Then you may call CompactDatabase method of the JetEngine object.
jet.CompactDatabase( strSourceCnn, strDestCnn )
if jet.Failed then alert jet.LastError

If the operation completes successfully Jet creates a new file of the specified type with the specified password if any. You may use any file management xtras to move newly created file into the original location. You can also move the file with VbScriptXtra and FileSystemObject.

-- Creating a new instance of FileSystemObject
fso = vb.CreateObject( "Scripting.FileSystemObject" )
-- Deleting old source file
fso.DeleteFile( "D:\Temp\DB.mdb" )
-- Moving new file in place of the old one
fso.MoveFile( "D:\Temp\DB2.mdb", "D:\Temp\DB.mdb" )

Where to find more info about Jet library?

VbScriptXtra provides autodocumentation feature allowing you to see what you can do with particular object instance. Just create a recordset object and type in Messages window:
put interface(jet)
ObjectBrowser xtra - autodocumentation companion for VbScriptXtra - will show all available interfaces, methods, properties and enumerations.

ObjectBrowser xtra is a part of ActiveCompanionSet. It is available to Download.