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

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

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

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

This is a best Solution..

Thank's For Solving my Problem

Have a Nice day

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. 

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.