Comments

1. osmond olli
   31 Jul 2008 at 16:26

   Hi , i have an ms access 97 compact database, i want to uncompact the same. because i could not able to see the source code of vb for the forms. i just able to run only. i would like to see the code behind the vb forms and reports.

   is there any way can it be possible to view the design and code of the forms and reports.

   thanks a lot in advance.

    

    

    

2. Mike J
   24 Jul 2008 at 09:51

   Thank you for the compliment I should tell you, and any others who aready noticed, the dual step in this. 

   The function  creates a duplicate and actually performs the compression on the file copy rather that the original.

   Following my experience with slowpoking MS Access running on windows servers for various old DSN database purposes using ASP, I decided to do it this way to avoid working on an original until the compression is done.

    

   Compression like this on a db exceeding 500,000 - 1,000,000 records in a single table such as an address database can take some time and hangs.

   If it hangs on a copy it's not to bad. When it hangs in the original, it can be very bad.

    

   As simple footnote and benchmark, I am now on a laptop with 512 MB RAM and 1,8 GHz cpu, and not even my 2,4GHz Dual core with 1 GB ram can compress some of my databases faster than within 20 minutes.

   And that is only on some 2,2 million records in an address list. Nothing fancy and 17 fields (1 text index) only.

   That's the nature of the beast when working in MS Access.

   MySQL on the other hand, using VB, could fix the same thing on these machines in a few seconds.

   Exporting and backing up the data always takes me less than 15 seconds to completion, and using ADO on MyODBC you get a better performance overall.

   Not bad for a free alternative and the connection strings and VB functions are just about as simple as for MS Access. 

3. zawmn83
   24 Jul 2008 at 05:34

   This is a best Solution..

   Thank's For Solving my Problem

   Have a Nice day

4. Mike J
   23 Jul 2008 at 16:57

   this was only made for MS Access 97-2000 but might work on 2003 as well.

   2007 changed the connection string property.

    you can find those at connectionstrings.com

    

   call the function as follows...

   CompactRepairAccessDB "C:\MyDatabase.mdb", "MyPassword" 

    if you have a password

   CompactRepairAccessDB "C:\MyDatabase.mdb", "" 

    if you have NO password

   or

   CompactRepairAccessDB "C:\MyDatabase.mdb"

   
   

   ---I use this thing daily right now moving old MS Access data to MySQL---------
   

   Public Sub CompactRepairAccessDB(ByVal sDBFILE As String,
               Optional sPASSWORD As String = "")
   
   Dim sDBPATH As String, sDBNAME As String, sDB As String, sDBtmp As String
   sDBNAME = sDBFILE 'extrapulate the file name
   
   Do While InStr(1, sDBNAME, "\") <> 0
           sDBNAME = Right(sDBNAME, Len(sDBNAME) - InStr(1, sDBNAME, "\"))
   Loop
   
   'get the path name only
   sDBPATH = Left(sDBFILE, Len(sDBFILE) - Len(sDBNAME))
   sDB = sDBPATH & sDBNAME
   sDBtmp = sDBPATH & "tmp" & sDBNAME
   'Call the statement to execute compact and repair...
   If sPASSWORD <> "" Then
           Call DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)
   Else
           Call DBEngine.CompactDatabase(sDB, sDBtmp)
   End If
   'wait for the app to finish
           DoEvents
   'remove the uncompressed original
           Kill sDB
   'rename the compressed file to the original to restore for other functions
           Name sDBtmp As sDB
   End Sub
   

   
   

    

   
   

5. zawmn83
   23 Jul 2008 at 07:26

   I have problem in this code in (Call DBEngine.CompactDatabase(sDB, sDBtmp))

    

   Public Sub CompactRepairAccessDB(ByVal sDBFILE As String, Optional sPASSWORD As String = "")
       Dim sDBPATH As String, sDBNAME As String, sDB As String, sDBtmp As String
       sDBNAME = sDBFILE 'extrapulate the file name
       Do While InStr(1, sDBNAME, "\") & lt > 0
           sDBNAME = Right(sDBNAME, Len(sDBNAME) - InStr(1, sDBNAME, "\"))
       Loop
       'get the path name only
       sDBPATH = Left(sDBFILE, Len(sDBFILE) - Len(sDBNAME))
       sDB = sDBPATH & sDBNAME
       sDBtmp = sDBPATH & "tmp" & sDBNAME
      
       'Call the statement to execute compact and repair...
       If sPASSWORD & lt > "" Then
           Call DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)
       Else
           Call DBEngine.CompactDatabase(sDB, sDBtmp)
       End If
      
       'wait for the app to finish
               DoEvents
       'remove the uncompressed original
               Kill sDB
       'rename the compressed file to the original to restore for other functions
               Name sDBtmp As sDB
   End Sub

    

6. Gajanan Ramdas sirsat
   31 Mar 2006 at 12:42

   Dear sir

   U code and logic very good .

   i am using it in my project

   but i have one syntanctical problem in code: the code where syntax error within the underlined lines is as follows:

   Do While InStr(1, sDBNAME, "\") & lt; > 0
   
           sDBNAME = Right(sDBNAME, Len(sDBNAME) - InStr(1, sDBNAME, "\"))
   Loop
   

   If sPASSWORD & lt; > "" Then
   
           Call DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)
   Else
           Call DBEngine.CompactDatabase(sDB, sDBtmp)
   End If
   
   
   
   
   

   1) the statement  :> Do While InStr(1, sDBNAME, "\") & lt; > 0

   & lt;>0 what is this variable lt; This is giving the error.

   Please send me the proper syntax.

   Thanks in advance.

   Regards

   Gajanan R Sirsat

   email Id: sirsat_gajanan@yahoo.co.in

    

    

7. Developer Fusion Bot
   01 Jan 1999 at 00:00

   This thread is for discussions of Compact & repair MS Access database.