Is it possible to Compact an Access database using a Visual Basic Form?

[MilesNepo]

I need to compact my "Inventory.mdb" and I'd like to create an .exe file that
will compact this database. Can I use VB directly to compact the database or do I still have to call another .mdb that will compact my "Inventory.mdb" database?

[waqas_hussain]

Add Reference of Microsoft DAO 3.6 Library to your Project. You will get an Object Named DBEngine.
Use DBEngine.CompactDatabase Method to compact the database into another New Database File. Then Delete the Previous (Uncompacted) Database. rename the New Database File to the previous datbase name.

The compactdatabase method will use some other parameters. I don't remember them now, but you can refere to the online help for more details.

Waqas...

[Iouri]

You can compact using ADOX

To take advantage of JRO's CompactDatabase method, which will also repair
the database, set a reference the JRO library, Microsoft Jet And
Replication Objects 2.1 Library. Next, declare and set a JetEngine object
variable, like so

Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine

JRO.CompactDatabase _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\myData1.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\myDataNewName.mdb"_
& ";Jet OLEDB:Engine Type=4"


'Delete the original database
Kill "c:\myData1.mdb"
'Rename the file back to the original name
Name "c:\myDataNewName.mdb" As "c:\myData1.mdb"

Set JRO = Nothing

This command follows similar syntax as DAO's CompactDatabase method-you
must provide the old database name and the new database name. In the
case of JRO, however, you must also provide a valid connection string,
as well as indicate the type of Jet engine to compact the database as.
It used 4 to create a Jet Version 3.x database suitable for Access 97
users. If you work with Access 2000 use the value 5 to create a Jet
Version 4.x database.

'-----example with password--------
Dim JRO As JRO.JetEngine
Dim strSource As String
Dim strTarget As String

Set JRO = New JRO.JetEngine

strSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\MyDB1.mdb;" & _
"Jet OLEDB:Database Password=password"

strTarget = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
"C:\MyDB2.mdb;" & _
"Jet OLEDB:Engine Type=4;" & _
"Jet OLEDB:Database Password=password"

Call JRO.CompactDatabase(strSource, strTarget)



Iouri Boutchkine
iouri@hotsheet.com