Compact Access Database using VB.NET

[Tinkerers]

Hi,
I've searched the Microsoft site and struck out. I'm looking for a good way to compact an Access database, using Visual Basic .NET.

In VB6, I used this code:
DAODBEngine_definst.CompactDatabase(name1, name2)

In .NET, this code seems erratic and unreliable. Is there a new command to do this ?

Thanks for any help !
Paul

 

[RiverGuy]

Did the code do anything at all? And did you reference DAO in your project?

 

[Tinkerers]

Yes, the code seems to work sometimes. It does not create an error, but sometimes it just hangs. Other times, it works fine, but is very slow.

I have not referenced DAO, thought this was done automatically in .net

There must be a way to do this "compact" using .net instructions, rather than the old DAO instructions.

Any ideas ?

 

[RiverGuy]

Hmmm, I'm not sure about that, since you really couldn't do it with ADO. So I don't know if they would have put it back in ADO.Net.

You've used the same code in VB6 and it works good for you? Maybe you should keep that app in VB6, and call it from .NET, wait for it to finish, then go on with the rest of your .Net process.

 

[russgreen]

Sub CompactRepair()

Dim ReplaceDB As String 'true or false
Dim PathtoDB As String 'path to database
Dim ProjectDB As String 'name of database
Dim CompactDB As String 'name of compacted database


'create a clone database and compact that one. Leaves the original database alone
If ReplaceDB = False Then
Try
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine()

JRO.CompactDatabase( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PathtoDB & ProjectDB, _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PathtoDB & CompactDB _
& ";Jet OLEDB:Engine Type=5&quot

MsgBox("Database succesfully compacted and repaired!" & (Chr(10)) & _
" " & (Chr(10)) & _
"The existing database has remained intact, the" & (Chr(10)) & _
"compacted and repaired database has been saved " & (Chr(10)) & _
"as:" & (Chr(10)) & _
" " & (Chr(10)) & _
PathtoDB & CompactDB, MsgBoxStyle.OKOnly, "Compact and Repair&quot

Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message & (Chr(10)) & _
" " & (Chr(10)) & _
"Delete the following file and try again: " & (Chr(10)) & _
" " & (Chr(10)) & _
PathtoDB & CompactDB & (Chr(10)) & _
" " & (Chr(10)) & _
"Or change the Compact and Repair settings in the Prefernces dialogue.", MsgBoxStyle.OKOnly, "Compact and Repair&quot
End Try

'compacts the original database after first making a copy
ElseIf ReplaceDB = True Then
Try
'Archive the projects database in case of error
FileCopy(PathtoDB & ProjectDB, PathtoDB & ProjectDB & ".ARCHIVED - " & Common.MonthShort)

'rename db before compact
Try
MkDir("C:\pst-temp&quot
Catch
RmDir("C:\pst-temp&quot
MkDir("C:\pst-temp&quot
End Try

Rename(PathtoDB & ProjectDB, "C:\pst-temp\tmp.mdb&quot

'compact and repair db
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine()

JRO.CompactDatabase( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\pst-temp\tmp.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PathtoDB & ProjectDB _
& ";Jet OLEDB:Engine Type=5&quot

'tidy up files
Kill("C:\pst-temp\tmp.mdb&quot
RmDir("C:\pst-temp\&quot

MsgBox("Database succesfully compacted and repaired!", MsgBoxStyle.OKOnly, "Compact and Repair&quot

Catch ex As Exception
MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OKOnly, "Compact and Repair&quot
End Try
End If

End Sub

 

[rborel]

Russ,
I tried your code (I need to do the same thing) and get an error with the "Dim JRO As JRO.JetEngine" section. The error is "Type JRO.JetEngine is not defined" I'm sure it's something I'm doing wrong.

Any suggestions?

 

[rborel]

You need to reference the "Microsoft Jet and Replication Objects 2.6 Library" under COM objects. to get rid of that error.