Search Tools Links Login

Repair and Compact an Access Database using JRO


Visual Basic 6, or VB Classic

This code will let you repair and compact an Access 97 or 2000 (haven't tested on other versions yet) database. Using JRO, when you call the CompactDatabase function, it automatically repairs the database first. You must have a reference to Microsoft Jet And Replication Objects x.x Library in your project.
Also, where I have a DoEvents in the code, there really should be some routine that checks to see when the file is actually deleted. I've tested this on databases that are about 5 megs in size with no problem. If anyone has any ideas (maybe a routine that checks to see when the file is unlocked or deleted), let me now. Thanks and enjoy!!

Original Author: unknown

Code

Option Explicit




'Must have reference to Microsoft Jet And Replication Objects x.x Library


Public Sub CompactDB(DBName As String)



Dim jr As jro.JetEngine

Dim strOld As String, strNew As String

Dim x As Integer



Set jr = New jro.JetEngine



strOld = DBName

x = InStrRev(strOld, "")

strNew = Left(strOld, x)

strNew = strNew & "chngMe.mdb"



'Use Engine Type = 4 for Access 97, Engine Type = 5 for Access 2000

jr.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strOld,
_

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNew & ";Jet
OLEDB:Engine Type=4"



Kill strOld

DoEvents

Name strNew As strOld



Set jr = Nothing



End Sub


About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 345 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.