Compact Access Database 
Author Message
 Compact Access Database
Hi,

How do I compact Access97 Database using VB code.
We are using VB6.0 and ADO 2.5

Any suggestion is honored...

Shrikant



Wed, 04 Feb 2004 00:55:23 GMT
 Compact Access Database

HOWTO: Compacting Microsoft Access Database via ADO

http://support.microsoft.com/support/kb/articles/Q230/5/01.ASP?LN=EN-...
n&FR=0

HTH

--
Patrick Logan, MCSD

Opinions expressed are my own and not necessarily those of my employer.



Wed, 04 Feb 2004 02:10:57 GMT
 Compact Access Database
Hi Shrikant,

Although ADO specification does not provide objects to compact or repair
Microsoft Access databases, this capability can be achieved by using the
ADO extension: Microsoft Jet OLE DB Provider and Replication Objects (JRO).
This capability was implemented for the first time in the JET OLE DB
Provider version 4.0 (Msjetoledb40.dll) and JRO version 2.1 (Msjro.dll).
Here are some sample codes for your reference:

1. In the Visual Basic IDE, on the Projects menu, click References.

2. Add Microsoft Jet and Replication Objects X.X library, where (X.X is
greater than or equal to 2.1).

Private Sub Command1_Click()

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

End Sub

Regards,
-Chali



Wed, 04 Feb 2004 23:59:06 GMT
 Compact Access Database
Copy paste this into a code module

Public Sub Compact_Database(sDatabase As String)
' This sub requires references be set to the
' Microsoft Jet and Replication Objects X.X library,
' where X.X is greater than or equal to 2.1.
' and the Microsoft Scripting Runtime Library
' The sub is based on article KBQ230501
On Error GoTo Compact_Database_Error

Dim sLng1 As Long, sLng2 As Long
Dim sShortName As String, sExt As String
Dim FileInfo As file
Dim fso As FileSystemObject

Set fso = New FileSystemObject
sShortName = fso.GetBaseName(sDatabase)
sExt = "." & fso.GetExtensionName(sDatabase)
sShortName = sShortName & sExt

'get the original file size
Set FileInfo = fso.GetFile(sDatabase)
sLng1 = FileInfo.SIZE

'create a temporary folder for holding the compacted database
If Not fso.FolderExists("C:\dbTemp") Then
    fso.CreateFolder "C:\dbTemp"
End If

'create a permanent folder for retaining a backup copy of
'the original database file.
If Not fso.FolderExists(App.Path & "\Temp") Then
    fso.CreateFolder (App.Path & "\Temp")
End If

' create a backup of the uncompacted database file
fso.CopyFile sDatabase, App.Path & "\Temp\" & sShortName, True

Dim jro As jro.JetEngine
Set jro = New jro.JetEngine

'compact the database file and save it to the C:\dbTemp folder
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & sDatabase, _
                    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=C:\dbTemp\" & sShortName & _
                    ";Jet OLEDB:Engine Type=4"

'overwrite the original database file
'with the compacted database file
fso.CopyFile "C:\dbTemp\" & sShortName, sDatabase, True

' delete the compacted database in the dbTemp folder
fso.DeleteFile "C:\dbTemp\" & sShortName

'get the compacted file size
Set FileInfo = fso.GetFile(sDatabase)
sLng2 = FileInfo.SIZE

MsgBox "The Database has been successfully compacted." & _
        vbCrLf & vbCrLf & "The original database file size was " & _
        (sStr1 / 1000) & " KB" & vbCrLf & vbCrLf & _
        "The new database file size is " & (sStr2 / 1000) & " KB", _
        vbInformation, "Done"

Compact_Database_Exit:
    Exit Sub
Compact_Database_Error:
    eError
End Sub

Quote:

> Hi,

> How do I compact Access97 Database using VB code.
> We are using VB6.0 and ADO 2.5

> Any suggestion is honored...

> Shrikant



Thu, 05 Feb 2004 09:52:25 GMT
 Compact Access Database
Small error on the preceeding code
Over paste the MsgBox with the following:

MsgBox "The Database has been successfully compacted." & _
        vbCrLf & vbCrLf & "The original database file size was " & _
        (sLng1 / 1000) & " KB" & vbCrLf & vbCrLf & _
        "The new database file size is " & (sLng2 / 1000) & " KB", _
        vbInformation, "Done"

Sorry for the mistake.

Quote:

> Copy paste this into a code module

> Public Sub Compact_Database(sDatabase As String)
> ' This sub requires references be set to the
> ' Microsoft Jet and Replication Objects X.X library,
> ' where X.X is greater than or equal to 2.1.
> ' and the Microsoft Scripting Runtime Library
> ' The sub is based on article KBQ230501
> On Error GoTo Compact_Database_Error

> Dim sLng1 As Long, sLng2 As Long
> Dim sShortName As String, sExt As String
> Dim FileInfo As file
> Dim fso As FileSystemObject

> Set fso = New FileSystemObject
> sShortName = fso.GetBaseName(sDatabase)
> sExt = "." & fso.GetExtensionName(sDatabase)
> sShortName = sShortName & sExt

> 'get the original file size
> Set FileInfo = fso.GetFile(sDatabase)
> sLng1 = FileInfo.SIZE

> 'create a temporary folder for holding the compacted database
> If Not fso.FolderExists("C:\dbTemp") Then
>     fso.CreateFolder "C:\dbTemp"
> End If

> 'create a permanent folder for retaining a backup copy of
> 'the original database file.
> If Not fso.FolderExists(App.Path & "\Temp") Then
>     fso.CreateFolder (App.Path & "\Temp")
> End If

> ' create a backup of the uncompacted database file
> fso.CopyFile sDatabase, App.Path & "\Temp\" & sShortName, True

> Dim jro As jro.JetEngine
> Set jro = New jro.JetEngine

> 'compact the database file and save it to the C:\dbTemp folder
> jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                     "Data Source=" & sDatabase, _
>                     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
>                     "Data Source=C:\dbTemp\" & sShortName & _
>                     ";Jet OLEDB:Engine Type=4"

> 'overwrite the original database file
> 'with the compacted database file
> fso.CopyFile "C:\dbTemp\" & sShortName, sDatabase, True

> ' delete the compacted database in the dbTemp folder
> fso.DeleteFile "C:\dbTemp\" & sShortName

> 'get the compacted file size
> Set FileInfo = fso.GetFile(sDatabase)
> sLng2 = FileInfo.SIZE

> MsgBox "The Database has been successfully compacted." & _
>         vbCrLf & vbCrLf & "The original database file size was " & _
>         (sStr1 / 1000) & " KB" & vbCrLf & vbCrLf & _
>         "The new database file size is " & (sStr2 / 1000) & " KB", _
>         vbInformation, "Done"

> Compact_Database_Exit:
>     Exit Sub
> Compact_Database_Error:
>     eError
> End Sub


> > Hi,

> > How do I compact Access97 Database using VB code.
> > We are using VB6.0 and ADO 2.5

> > Any suggestion is honored...

> > Shrikant



Thu, 05 Feb 2004 10:02:13 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Compact Access Database Using ADOX

2. Error when compacting Access Database using JRO

3. Compacting Access DataBase

4. Compacting Access databases from a VVB App

5. compact Access database using ADO

6. Compacting access database from ODBC

7. Compacting ACCESS database - Progress bar?

8. Compact Access Database from VB6?

9. Compacting Access Database

10. Compacting Access Database from VB

11. Compacting Access database with password

12. compacting Access database without DAO


 
Powered by phpBB® Forum Software