|
|
Home » Visual Basic » Article
Visual Basic Compact/Repair Access Database Utility
|
| Article by: | Kenneth Ives (3/29/2003) |
|
| Summary: | This utility will compact and repair the access database you select. It is actually more convenient to use this utility than do the same task with Access, because you don't have to save the database with a different name and then delete the old database when you are done: here, a temporary copy of the database is automatically created and deleted. |
|
| Viewed: 91327 times |
Rating (22 votes): |
|
4 out of 5 |
|
|
|
Visual Basic Compact/Repair Access Database Utility
Download Now (237KB)
This utility will compact and repair the access database you select. It is actually more convenient to use this utility than do the same task with Access, because you don't have to save the database with a different name and then delete the old database when you are done: here, a temporary copy of the database is automatically created and deleted.
In addition, the utility reports the file space freed up and the compression percentage when it is done. Documentation and comments are very good.
Be sure to also make a reference in the IDE to:
Microsoft ActiveX Data Objects 2.x Library
Microsoft Jet and Replication Objects 2.x Library
Important information:
YOU MUST KNOW THE TYPE OF DATABASE TO BE COMPACTED!
If you compact an earlier database with a higher version type, the database
will be corrupted forever.
This application will compact and repair MS Access (*.mdb) databases only.
Features:
- Now has the capability to use the windows interface or open a DOS window
and use the command line. If used in a batch file then Windows must be
running.
- Logs start and finish times to a log file to be determined by the user.
If this parameter is left blank, then the folder where the database
resides is where the log file will be created. Error messages are
also logged.
- If using command line parameters, no message boxes should appear. All
information will be logged to the log file. (_YYYYMM.log) See sample
log file. Any batch processing must be run in a DOS window.
- User defined work folder where the compact/repair process will take
place. If this parameter is left blank, then the folder where the
database resides is where the processing will take place.
CompMDB Command Line Options
***** IMPORTANT *****
YOU MUST KNOW THE TYPE OF DATABASE OR IT MAY BECOME CORRUPTED FOREVER!
Syntax:
CompMDB /Ffilename /Ssource_folder /Ttype_of_database
[/Llog_folder] [/Wwork_folder] [/Ddelete_backup]
CompMDB /sC:\Program Files\Microsoft Office /T5 /Fnorthwind.mdb
Note: All required parameters must be entered. You can enter the parameters
in any order and you do not have to separate them with spaces. Double
quotes do not have to be used to wrap long filenames.
***** IMPORTANT *****
YOU MUST KNOW THE TYPE OF DATABASE OR IT MAY BECOME CORRUPTED FOREVER!
Required prameters:
/F Name of the database (Without any path information)
/S Path to where the database resides
/T Type of database (4 = Access 95/97 5 = Access 2000/2002)
Optional parameters:
/D Y or N to delete backup copy of database after the
compact/repair has completed. Default = "N"
/L Path to log folder. If missing, /S parameter will be used.
/W Path to temp work folder. If missing, /S parameter will be used.
/? or ? This online help message
Important: There are no warning messages when the process is running.
Verify the type of database and enter the appropriate value.
Any batch processing must be run in a DOS window not a DOS prompt.
Sample batch file:
@echo off
cls
REM ******************************************************************************
REM ** DB name is MyDatabase.mdb Access 2000 Type 5 Delete backup when finished
REM ******************************************************************************
echo. Compacting MyDatabase.mdb
CompMDB.exe /dMyDatabase.mdb /T5 /sD:\Misc Databases /LC:\Log2001 /DY /wC:\WorkArea
REM ******************************************************************************
REM ** DB name is Archive.mdb Access 95 Type 4 Delete backup when finished
REM ******************************************************************************
echo. Compacting Archive.mdb
CompMDB.exe /dArchive.mdb /T4 /sD:\Misc Databases /LC:\Log2001 /DY /wC:\WorkArea
REM ******************************************************************************
REM ** DB name is John.mdb Access97 Type 4 Do not delete backup when finished
REM ******************************************************************************
echo. Compacting John.mdb
CompMDB.exe /dJohn.mdb /T4 /sD:\Misc Databases /LC:\Log2001 /wC:\WorkArea
echo. Finshed!
echo.
|
|
|
View highlighted Comments
 User Comments on 'Visual Basic Compact/Repair Access Database Utility'
|
Posted by :
 Archive Import (Adnan) at 22:35 on Monday, June 09, 2003
|
The article presents a good idea abt compact and repair but I tried the same with a password protected database and I am unable to figure out the parameter string for compatcing the same
| |
|
Posted by :
Archive Import (hesterloli) at 16:49 on Tuesday, June 10, 2003
|
Well using the executable is all well and good. How about how to do this in VBA? Thanks.
| |
|
Posted by :
Archive Import (ponyboy) at 13:48 on Thursday, June 19, 2003
|
Very clear and concise article. It was very helpful. Great work.
| |
|
Posted by :
Archive Import (Doug Stone) at 12:00 on Friday, July 25, 2003
|
The article does not mention anything about the FsoKci.dll wrapper that Ken has written that needs to exist for the application to run.
| |
|
Posted by :
Archive Import (newcitysteve) at 15:29 on Monday, August 11, 2003
|
In VB you can use the "CompactDatabase" or "RepairDatabase" statements. I didn't check VBA.
| |
Posted by :
 randg at 08:20 on Wednesday, November 26, 2003
|
This works fine on the m2000.mdb supplied with the download but when I tried this on my secured backend I keep getting the error in log file "connect string failure. failed to compact etc ..."
My path is correct and there are no errors when I run this from a batch file.
Any ideas ?
| |
|
Posted by :
jaydeep at 07:21 on Wednesday, August 17, 2005
|
how does one repair an password protected access database through a vb exe (the password is known) - how does one pass it through the exe?
thanks
| |
|
Posted by :
abrar at 06:31 on Saturday, April 07, 2007
|
i want to compact Password protected MDB using VB6 i have used the fowwloing code. all
the modules of the application working fine but databse compression module not working.
it generate error "Could not find installable ISAM."
can any body help me
the code that i have used for compression
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strWorkDB & ";Jet OLEDBDatabase pssword=abc", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & mstrBakDB & ";Jet OLEDB:Encrypt Database=True;Engine Type=3;Jet OLEDBatabase Password=abc"
abrar_s130@yahoo.com
| |
|
Posted by :
shrekk at 04:35 on Friday, August 01, 2008
|
Hi,
Very good post. Here is one more option to repair and compact your database. You can try stellar phoenix access recovery software to repair your damaged mdb files. Best thing about this software is demo version will show the preview of repaired mdb files. Download demo version from here: http://www.stellarinfo.com/access-recovery.htm
Thanks
| |
|
To post comments you need to become a member. If you are already a member, please log in .
 
| RELATED ARTICLES |
Generate License Keys (such as CD keys on Microsoft software)
by Brian Gillham
This class allows you to generate license keys, such as the CD keys on the case of most Microsoft software |
 |
Communicating with the Database (Using ADO)
by Chaudhary Pradeep K. Roy
Using ADO to get connected with the database. |
|
Visual Basic Read and Modify the Registry
by Kenneth Ives
Perform the four basic functions to the Windows registry. Add, change, delete, and query. Allows you to to read registry values, and modify both keys and values.
|
|
Generate an Array of Unique Random Numbers
by Brian Gillham
This function will generate an ARRAY of TRULY random numbers. |
|
Beginning Resource Files in Visual Basic
by Kenneth Ives
Have you ever wanted to use graphics, such as icons, bitmaps, cursors, and AVI files? How about sound or even message box text? This can be an enormous amount of overhead. These are all examples of more files to keep track of when you distribute an application, DLL, or OCX. Lets find out how resource files can help.... |
|
Visual Basic Compact/Repair Access Database Utility
by Kenneth Ives
This utility will compact and repair the access database you select. It is actually more convenient to use this utility than do the same task with Access, because you don't have to save the database with a different name and then delete the old database when you are done: here, a temporary copy of the database is automatically created and deleted. |
|
Creating a watermark in Excel with VBA
by Kenneth Ives
Adding a watermark to an Excel spreadsheet using VBA. |
|
Change Cursor to Hour Glass
by Brian Gillham
A simple script to change the cursor to an hour glass. |
|
Generate your own Random Numbers
by Thaha Hussain
This simple program generates random numbers using the basic mathematical methods. |
|
Analog Clock
by Thaha Hussain
This program demonstrates Thaha Hussain's Clock Work Formula to paint an Analogue Clock. |
|
| |
