Community discussion forum

Compact & repair MS Access database

• • Reply

  10 years ago

  by Developer Fusion Bot

  Developer Fusion Bot

  , United Kingdom

  Joined 2 years ago

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

  Report abuse
• Advertisement

  Simply the fastest line-level profiler for .NET ever

  “The low overhead means it has minimal impact on the execution of my program”
  Mark Everest, Development Team Leader, Renault F1 Team Ltd.

  Try out the new ANTS Profiler 4 for yourself. Download your 14-day trial now

• • Reply

  2 years ago

  by Gajanan

  Gajanan Ramdas sirsat

  , India

  Joined 2 years ago

  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

   

   

  Report abuse
• • Reply

  5 months ago

  by zawmn83

  zawmn83

  , Myanmar

  Joined 8 months ago

  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

   

  Report abuse
• • Reply

  5 months ago

  by mrjdesign

  Mike J

  London, UK, United Kingdom

  Joined 7 years ago

  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
  

  
  

   

  
  

  Report abuse
• • Reply

  5 months ago

  by zawmn83

  zawmn83

  , Myanmar

  Joined 8 months ago

  This is a best Solution..

  Thank's For Solving my Problem

  Have a Nice day

  Report abuse
• • Reply

  5 months ago

  by mrjdesign

  Mike J

  London, UK, United Kingdom

  Joined 7 years ago

  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. 

  Report abuse
• • Reply

  5 months ago

  by ozziekun

  osmond olli

  , Trinidad and Tobago

  Joined 8 months ago

  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.

   

   

   

  Report abuse