how can repair / compact my access97 db from my app vb6

[sitmapa]

My database adds and deletes data frequently. As you know, this causes the size of the database to balloon up to a system halting size.

HOW CAN repair and compact my mdb 97 from vb.

because, sometimes, i get the error, that the database files have a no valid format or not is a msaccess db.

i need repair / compact from my vb app,,,

somebody have any sample or idea, how can do that...

thank´s for any help or suggestion

 

[chogben]

I think in Access 97 you can use command line arguments, you'll have to raid the help as I've got 2000 here. You also want to look at the shell command to start up access with those parameters.

There are other ways of doing it, but this is one of the simpliest.

HTH

 

[petermeachem]

Add a reference to JRo. This code will get you started. It's wise to copy the mdb first and then you can copy back should anything awful happen.

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

sSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cDataPath & cdb & ".mdb"
sDestDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cDataPath & "temp.mdb"
JRO.CompactDatabase sSourceDB, sDestDB & ";Jet OLEDB:Engine Type=4"


Engine type 4 means access97, if you leave that out it ends up as access 2000. Peter Meachem
peter @ accuflight.com

 

[phinoppix]

Hi!

Add a Microsoft DAO 2.5/3.5 Compatibility Library and view the methods under DBEngine class (use the Object Browser).

Look for CompactDatabase.
Here's a sample code:

Option Explicit
Const strDB = "C:\Program Files\" & _
                "Microsoft Visual Studio\" & _
                "VB98\BIBLIO.MDB"
                
Dim db As DAO.Database
Dim rs As DAO.Recordset

Private Sub Command1_Click()
    Dim strTmp As String
    
    'Temporary filename
    strTmp = "C:\WINDOWS\TEMP\DBTMP.MDB"
    MousePointer = vbHourglass
    Command1.Enabled = False
    
    
    On Error GoTo Err_Handler
    
    'If temporary file is already existing,
    '  must delete or error occurs at CompactDatabase
    
    If Dir(strTmp) <> &quot;&quot; Then Kill strTmp
    CompactDatabase strDB, strTmp
    
    'If compacting is successful,
    '   delete the original, uncompacted file
    Kill strDB
    
    'Copy the temporary, compacted file
    '  to replace the original file
    FileCopy strTmp, strDB
    
    
Err_Handler:
    If Err > 0 Then MsgBox Err.Description
    MousePointer = vbDefault
    Command1.Enabled = True
End Sub

Hope this helps! Better yet, explore VB's help on this. VBA help also has this topic.

Anybody out there knows the ADO counterpart???

 

[Guest_imported]

ok,

what command repair the database....

because, when i try to compact the db, i get the error like the files no is a valid format.... but, if i copy this file and open in my computer with msaccess program.... if get the message that the dabase is damaged and need to be repait, i press the ok button, and all is ok...

but, i can´t do that in the machine of the client,,,

the machine have not the msaccess programm.... only have the mdb files.....

thank´s for any aditional commen or idea...

 

[phinoppix]

CompactDatabase is just one of the many DB methods of the DBEngine object. If you use the Object Browser, you'll find another method (the one you need), RepairDatabase. Better read VB documentation on this.

Syntax:
Sub RepairDatabase(Name As String)

If you use P&D Wizard to make your setup files, you don't have to worry about other units having no Access because VB will include ALL the necessary COMs, including the DAO DLL you linked to your VB App.

Better make an error handler. Call the RepairDatabase method only if the database you're trying to compact is corrupted. When you attempt to compact a corrupt database like the one you just had, get the error number and trap it. In the error handler under corrupt-db-error-number, call the RepairDatabase. If successful, resume compacting.

[tt]
Err_Handler:
'nnnnnnnn is the db-corrupt-error-number
If Err = nnnnnnnnn Then
Call RepairDatabase(strDB)
'If successful, return to CompactDatabase line
Resume
ElseIf Err > 0 Then
MsgBox Err.Description
End If
MousePointer = vbDefault
Command1.Enabled = True
[/tt]

Hope this helps!

 

[GDGarth]

The easiest thing to do is create a batch file that starts your database using the command line switches to compact or repair. Search the Access help file to find the exact command line switch, but to compact, I think you just put "/ compact" in the shortcut.

I use this every day at work to compact a database after a nightly data update.

 

[phinoppix]

Yes, that would be nice for as long you have MS Access installed in the system. But sitmapa said...
quote:
the machine have not the msaccess programm.... only have the mdb files.....

It's good practice to anticipate systems with VERY minimal installation.

 

[sitmapa]

alphanytz

i included the dao 3.6 reference to my project...

and in the .methods no show the repairdatabase....

what dao reference have the repairdatabase method.....
or what objects have this methods....

thank´s for your response

 

[phinoppix]

Um, forgot to specify Microsoft DAO 2.5/3.51 Compatibility Library.

 

[strongm]

Alphanytz, petermeacham has already given the 'ADO' solution, which involves using the JRO library (Microsoft Jet and Replication Objects. JRO's CompactDatabase method both compacts and repairs.