Repair and Compress Access Database in VB 1

[Youngguy]

The commands I was using in DAO 3.51 are not working in DAO 3.6 to Repair and Compress an MicroSoft Access Database. Any tips as to what will work?

 

[Niv3k]

Yeah, look up the Jet Replication Object (JRO Library) They should have good info in the MSDN on repairing and compacting a DB from VB, regardless of which version of DAO you use.

Kevin

 

[Jonathan123]

I am having the same problem.
I just saw the post above to use JRO but I can't seem to find any documentation on it.
Help!

 

[Niv3k]

I went to Microsofts site and they have what I believe is the answer to your query here:

http://support.microsoft.com/support/kb/articles/Q230/5/01.ASP

Good Luck!

Kevin

 

[Youngguy]

I looked in MSDN and couldn't find the JRO Library. Where else should I look?

 

[Niv3k]

I believe it is listed under the References dialogue as Microsoft Jet and Replication objects 2.5 library, and the filename is msjro.dll. It should be there on your system somewhere, if it is not I can email the .dll to you if you would like.

 

[Youngguy]

I have looked in the MSDN library and could not find JRO. Any more ideas?

 

[Jonathan123]

Just did it and the database when opened needs to be repaired. I have tried 2 different ways to compact it

1) Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\nwind2.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\abbc2.mdb;Jet OLEDB:Engine Type=4"

2) DBEngine.CompactdataBase sDatabasePath & sDatabaseName, sDatabasePath & sTempDatabase

Both leave me with a mdb that needs to be repaired
VB gets a runtime error that says it needs to be repaired and when I open it in Access it tells me the same.

 

[Niv3k]

Have you downloaded MDAC 2.6 sp1 and Jet 4.0 sp3 from :

http://www.microsoft.com/data/

Try downloading the latest service packs, and try it again. Also, maybe someone opened an Access 97 db in Access 2000, converted it, and now you are trying to open it in 97? That's a thought. Or the database (Johnathon123) might be screwed up beyond all recognition. If the database is on a shared network drive, I would copy it to my local machine and try the repair again.

These are reasons 1, 2, and 3 I upgraded to SQL Server.

 

[Jonathan123]

When it is fixed, it is fine. The only issue is when I compact it again it messes it up.
The compact feature in code is what is messing it up.
I am currently the only user as it is still in development stages so that rules out many possiblities.

 

[Guest_imported]

Update to MDAC 2.6 and Jet 4.0. The following does work...

Private Sub mnuRepairDatabase_Click()

Dim fso As New FileSystemObject, File1 As File
Dim je As New JRO.JetEngine

'Set objDB = Nothing 'only used if the database is opened from a class module

' Make sure that a file doesn't exist with the name of
' the compacted database.
If Dir(App.Path & &quot;\Sys\MyDB2.mdb&quot <> &quot;&quot; Then
Kill App.Path & &quot;\Sys\MyDB2.mdb&quot;
End If

On Error GoTo errorhandler
' Compacts and encrypts version MyDB database.
je.CompactDatabase _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source= &quot; & App.Path & &quot;\Sys\MyDB.mdb&quot;, _
&quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source= &quot; & App.Path & &quot;\Sys\MyDB2.mdb;&quot; & _
&quot;Jet OLEDB:Encrypt Database=True&quot;

If Dir(App.Path & &quot;\Sys\MyDB2.mdb&quot <> &quot;&quot; Then
Kill App.Path & &quot;\Sys\MyDB.mdb&quot;
Set File1 = fso.GetFile(App.Path & &quot;\Sys\MyDB2.mdb&quot
File1.Copy App.Path & &quot;\Sys\MyDB.mdb&quot;
End If

MsgBox &quot;Database Repair Complete&quot;
Exit Sub
errorhandler:
If Err = -2147467259 Then
'Err.Description
MsgBox &quot;Error# &quot; & Err & &quot; -> &quot; & Err.Description
End If

End Sub

If the database is open by another user, the error will occur

Good Luck