compacting databases

[sharat]

Hi All,

I have an MS-ACCESS 2000 databases which need to compacted.
I know there are only 2 methods: by ADO and by DAO.
My Q is which method is good in performance and speed wise?
I tried with both of them but not able to judge which is a better choice.

Any help is appreciated,
TIA
sharat

[sharat]

FYI
I am using WIN 2000 on NT and VB 6.0

TIA,
sharat

[Mandelbrot]

You can get Access 2000 databases to self-compact on exit. Have a look in the options (Tools / Options...).

[cpradio]

Originally posted by oh1mie
Try this:

Public Sub subCompactDatabase(DbFile As String, Optional Versio As Integer = 5)
    
   'Add Microsoft Jet and Replication Objects 2.x Library to references
    Screen.MousePointer = vbHourglass
    
    On Local Error Resume Next
        
    Dim strScoreFile       As String
    Dim strScoreFileBackup As String
    Dim JRO               As JRO.JetEngine
    
    strScoreFile = DbFile
    strScoreFileBackup = Left(strScoreFile, Len(strScoreFile) - 3) & "~db"
    
    Kill strScoreFileBackup
    Name strScoreFile As strScoreFileBackup
    
    Set JRO = New JRO.JetEngine
    JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strScoreFileBackup, _
                        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strScoreFile & ";Jet OLEDB:Engine Type=" & Versio
    
    Kill strScoreFileBackup
    Screen.MousePointer = vbDefault
    
End Sub

Quick question. How can I find the file size of the database before I compacted it and then grab it again after compacting it?

[oh1mie]

You can use Api:

Option Explicit
 
Public Const MAX_PATH As Long = 260
Public Const INVALID_HANDLE_VALUE As Long = -1
 
Public Type FILETIME
   dwLowDateTime  As Long
   dwHighDateTime As Long
End Type
 
Public Type WIN32_FIND_DATA
   dwFileAttributes As Long
   ftCreationTime   As FILETIME
   ftLastAccessTime As FILETIME
   ftLastWriteTime  As FILETIME
   nFileSizeHigh    As Long
   nFileSizeLow     As Long
   dwReserved0      As Long
   dwReserved1      As Long
   cFileName        As String * MAX_PATH
   cAlternate       As String * 14
End Type
 
Public Declare Function FindFirstFile Lib "kernel32" _
   Alias "FindFirstFileA" _
  (ByVal lpFileName As String, _
   lpFindFileData As WIN32_FIND_DATA) As Long
 
Public Declare Function FindClose Lib "kernel32" _
  (ByVal hFindFile As Long) As Long
Public Function fcnFileSize(TargetFile As String) As Long
   
   On Local Error Resume Next
 
   Dim WFD As WIN32_FIND_DATA
   Dim hFile As Long
   
   hFile = FindFirstFile(TargetFile, WFD)
   If hFile <> INVALID_HANDLE_VALUE Then
      fcnFileSize = WFD.nFileSizeLow
   End If
   Call FindClose(hFile)
   
End Function

[cpradio]

I ended up just making a copy of the database file as an option instead of compacting the database. For some reason I kept getting too old of a version error or something.

-Matt

[frigginjerk]

thought this might also be of some help.

http://www.gab2001uk.com/visualbasic/daovsado/index.htm

this site proves that DAO is about 3 times faster than ADO.