Compact & Repair Database(access) 1

[sn00pies]

hello,

i need to compact and repair database by using Visual Basic.. give me some hints please. thanks

 

[Sumitdev]

Have you tried using DBEngine.CompactDatabase? You will need Microsoft DAO 3.6 Object Library selected as a reference.

 

[PGoRule]

Using DAO
Using ADO

Sharing the best from my side...

--Prashant--

 

[jadams0173]

use JRO (jet replication objects 2.x library)

 

[SmallTalker]

Sumitdev's DBEngine.CompactDatabase works for DAO and Access.

if so:

1. Delete the contents of temporary tables.
2. Repair the database (member of DBEngine too)
3. Compact the Database

If you database is corrupt the compact-statement will fail!
Please always repair the database before you compact it.

If this doesn't help, you have to manually repair the database with the Acesss-App.

SmallTalker

 

[sn00pies]

hi... These are my codes for compact & repair but i seem to face an error msg with :

runtime error'-2147217843(80040e4d)':Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

This only happens when my database is passworded. The following codes can be run successfully only the database W/O passworded.

Public Function CompactAndRepairDB( _
Optional sSecurity As String, _
Optional sUser As String = "Admin", _
Optional sPassword As String = "1515", _
Optional lDestinationVersion As Long) As Boolean

Dim sCompactPart1 As String
Dim sCompactPart2 As String
Dim oJet As JRO.JetEngine

sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & App.Path & "\dbSpaMassage1.mdb" & _
";User Id=" & sUser & _
";Password=" & sPassword

sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & App.Path & "\New\dbSpaMassage1.mdb"

sCompactPart2 = sCompactPart2 & _
";Jet OLEDB:Engine Type= 5"

Set oJet = New JRO.JetEngine
oJet.CompactDatabase sCompactPart1, sCompactPart2
Set oJet = Nothing

CompactAndRepairDB = True
End Function

Can anyone guide me to fix this problem? thanks

 

[JerryKlmns]

From

http://www.connectionstrings.com/

Standard security:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;"


Workgroup (system database):
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDB:System Database=system.mdw;"


With password:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;Jet OLEDBatabase
Password=MyDbPassword;"

Modify your code to handle the connection string in an appropiate way.

 

[ProgramError]

Access has its own vba compact and repair command if that's any use?

(straight from the vba help files)

Example
The following example compacts and repairs a database, creates a log if there's any corruption in the source file, and returns a Boolean value based on whether the recovery was successful. For the example to work, you must pass it the paths and file names of the source and destination files.

Function RepairDatabase(strSource As String, _
        strDestination As String) As Boolean
        ' Input values: the paths and file names of
        ' the source and destination files.

    ' Trap for errors.
    On Error GoTo error_handler

    ' Compact and repair the database. Use the return value of
    ' the CompactRepair method to determine if the file was
    ' successfully compacted.
    RepairDatabase = _
        Application.CompactRepair( _
        LogFile:=True, _
        SourceFile:=strSource, _
        DestinationFile:=strDestination)

    ' Reset the error trap and exit the function.
    On Error GoTo 0
    Exit Function

' Return False if an error occurs.
error_handler:
    RepairDatabase = False

End Function

Ian Mayor (UK)
Program Error
Programming is 1% coding, 50% error checking and 49% sweat as your application bombs out in front of the client.

 

[tsdragon]

Very useful info. This thread is going in my archive.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[SBerthold]

>If you database is corrupt the compact-statement will fail!
>Please always repair the database before you compact it.

The RepairDatabase method is not directly supported by DAO 3.6 or JRO, but is incorporated into the CompactDatabase method. RepairDatabase was availible in DAO 3.5.
You should know that in contrast to DAO 3.5x, this is handeled differently under DAO 3.6 and JRO.
On an Access 7.0 Mdb, or on one converted to a '97 MDB (DAO 3.5x), it may be a be good idea to use Compact first, which will indeed correct some "corruption" errors, and then if that fails, then use Repair and then try to Compact again. Otherwise, using the repair first, the corrupt MDB may end up in a state where it corrupt beyond repair.

You should also know that DAO will report a corrupt mdb, when it really isn't corrupt, in a sense that the db is "broken" (such as bad indexes), but rather simply as a warning, such as when a (partially completed) transaction has (possibly) violated data integrity constraints determined by the programmer. In other words, when a transaction is niether finalized nor rolled back (possible due to an app. crash). In this case, a simple flag is left set to "True" in the mdb, which the Compact method will reset. Whether the data is actually (still) "corrupt", is left up to you to determine. Beyond that, is it possible that the Database is actually corrupt due to this crash (bad indexes).

>If this doesn't help, you have to manually repair the database with the Acesss-App.

There is also a JetComp.Exe from MS which a user can use to compact/repair the Mdb, even with out Access on the machine.

Also:
In the Jro example given, if you set the property "Jet OLEDB:Engine Type=", make sure you use the right type. If your mdb is a '97/DAO3.5 mdb and you specify the destination Engine Type=5, it will convert to Dao 3.6, and you will no longer be able to access it through dao 3.5.

 

[sn00pies]

thanks fellow programmers... good info.