Compact & repair w/VB6
Edward
using VB.
The following example fails with:
"Failed to compact database: Cannot perform this operation; features in this
version are not available in databases with older formats."
**Code used**
'Compact a JET database using ADO
'The following routines demonstrates how to compact a JET database using
ADO:
'Purpose : Compact a JET (Access) database using ADO
'Inputs : sDatabasePath The path to the database
path eg. C:\nwind.mdb
' [bEncryptDatabase] If True, encrypts the
contents of the database
'Outputs : Returns zero if successful, else returns error code
'Notes : Requires "Microsoft Jet and Replication Objects X.X
library",
' where (X.X is greater than or equal to 2.1)
' Compacts the database by creating a temporary database with
the extension .tmp then,
' if the compaction is successful, it overwrites the original
database.
' Will not work if anyone else is connected to the database.
Function DatabaseCompact(sDatabasePath As String, Optional bEncryptDatabase
As Boolean = False) As Long
Dim oJRO As Object 'JRO.JetEngine
On Error GoTo ErrFailed
If Len(Dir$(sDatabasePath & ".tmp")) Then
'Delete the existing temp database
VBA.Kill sDatabasePath & ".tmp"
End If
Set oJRO = CreateObject("JRO.JetEngine")
If bEncryptDatabase Then
'Compact and encrypt the database
oJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& sDatabasePath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sDatabasePath & ".tmp;Jet OLEDB:Encrypt Database=True"
Else
'Compact the database
oJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
& sDatabasePath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
sDatabasePath & ".tmp;Jet OLEDB:Engine Type=4"
End If
'Delete the existing database
VBA.Kill sDatabasePath
'Rename the compacted database
Name sDatabasePath & ".tmp" As sDatabasePath
Set oJRO = Nothing
Exit Function
ErrFailed:
Debug.Print "Failed to compact database: " & Err.Description
DatabaseCompact = Err.Number
Set oJRO = Nothing
On Error GoTo 0
End Function
*** End code ***
I have the 'Microsoft Jet & Replication Objects 2.6 Library' reference
enabled.
I have also tested it with 3.6 instead of the 4.0 jet references, same
result.
The following simple method works every time, but I'm not sure if it
Compacts AND Repairs as the ADO version claims:
Private Sub cmdCompSimp_Click()
DBEngine.CompactDatabase "C:\Sales.mdb", "c:\db2.mdb"
End Sub
Any suggestions appreciated.
Douglas J. Steele
Jet OLEDB:Engine Type=4 corresponds to DAO 3.x format.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Edward" <nos...@mail.com> wrote in message
news:fkbsl.53132$Rg3....@newsfe17.iad...
Edward
Thanks for your time.
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:OpycgQyn...@TK2MSFTNGP04.phx.gbl...
Edward
Is there a best method of compacting a database?
Like this simple line:
DBEngine.CompactDatabase "C:\dbmdb", "c:\db_cr.mdb"
Or the below ADO method?
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:OpycgQyn...@TK2MSFTNGP04.phx.gbl...
Douglas J. Steele
a difference (other than the fact that you need fewer references, which is
always a good thing)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Edward" <nos...@mail.com> wrote in message
news:hbxsl.115000$6r1....@newsfe19.iad...
eliza
2. Check if the database is in use , display a warning.
3. Create a temporary folder and copy the DB
4. Create an object of JRO JetEngine and call the compact method , to compact the tempdb and replace at the original location. After compacting delete the temp folder along with its contents.
Edward wrote:
Compact & repair w/VB6
06-Mar-09
Any suggestions appreciated.
Previous Posts In This Thread:
On Friday, March 06, 2009 10:30 AM
Edward wrote:
Compact & repair w/VB6
Any suggestions appreciated.
On Saturday, March 07, 2009 7:56 AM
Douglas J. Steele wrote:
Re: Compact & repair w/VB6
For Access 2000 or 2002/2003 format, use Jet OLEDB:Engine Type=5
Jet OLEDB:Engine Type=4 corresponds to DAO 3.x format.
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Edward" <nos...@mail.com> wrote in message
news:fkbsl.53132$Rg3....@newsfe17.iad...
On Saturday, March 07, 2009 11:10 AM
Edward wrote:
Oh my gosh, that solved it.Thanks for your time."Douglas J.
Oh my gosh, that solved it.
Thanks for your time.
On Saturday, March 07, 2009 11:22 AM
Edward wrote:
Re: Compact & repair w/VB6
A followup question:
Is there a best method of compacting a database?
Like this simple line:
DBEngine.CompactDatabase "C:\dbmdb", "c:\db_cr.mdb"
Or the below ADO method?
On Sunday, March 08, 2009 10:39 AM
Douglas J. Steele wrote:
I prefer using the CompactDatabase method, but I'm not sure there's much of a
I prefer using the CompactDatabase method, but I'm not sure there's much of
a difference (other than the fact that you need fewer references, which is
always a good thing)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Edward" <nos...@mail.com> wrote in message
news:hbxsl.115000$6r1....@newsfe19.iad...
Submitted via EggHeadCafe - Software Developer Portal of Choice
Free Online Courses Available for Eggheadcafe.com Users
http://www.eggheadcafe.com/tutorials/aspnet/5261083e-6e03-4b25-8728-fc3cf6855293/free-online-courses-avail.aspx