Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compacting (Access 2003)

    Hi,
    I have a client, who has a compacting problem. We have there database. DB1 holds Forms, Reports, Queries and VBA Code. DB2 holds 15 tables, and DB3 holds a Table. So the client opens DB1, which is a MDE, and produces reports and updates tables on DB2 and DB3, which are linked to DB1. The problem is DB3 grows very large, and I have to go in every fortnight and open DB3, enter its password, and open the table. After which I close DB3. Is there a way to compact DB3 from DB1?
    Thanks,
    Sarge

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compacting (Access 2003)

    Since DB1 is linked to DB3, chances are that DB3 is open when DB1 is open. You cannot compact a database while it's open (except the current database, but Access uses a trick for that).
    You can set DB3 to compact on close (in the General tab of Tools | Options), or you can create a scheduled task to compact it at regular intervals, for example during the night.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting (Access 2003)

    Hi Hans,
    I am kornfused. All of my databases are set to compact on close. That is why if I open DB3 up and shut it down it compacts. But when I open up DB1 and it links to DB3 and I close DB1. DB1 compacts, but DB3 does not. It sounds like I need to build a task as you call it. But how? I do not want to give the password to the client, and any task I build that opens DB3, wants the password. So I am kornfused!
    Kornfused Sarge

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compacting (Access 2003)

    A backend database should be compacted (if it is set to compact on close) as soon as the last user closes the frontend. So it DB3 doesn't compact, Access must think it's still in use. Can you check if an .ldb file remains behind in the folder containing DB3 when all users have closed their frontends? If so, try deleting the .ldb file. If not, DB3 appears to be corrupt.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting (Access 2003)

    Hi Hans,
    First there is only one user. There is no .ldb file and if you open DB3 by itself, it compacts. It is only when DB1 links to DB2 and DB3, that neither DB2 or DB3 compact. So do I take it that a database can not be compacted if it is link to? All databases are marked for compacting.
    Still kornfused,
    Sarge

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting (Access 2003)

    Hi Hans,
    Well I tried the utility, it did not work. So once again we tried every thing that could be done to help my friend and his client. Once again thanks for all your help.
    Sarge

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Compacting (Access 2003)

    a problem i have found is if you have a database already named DB1 and you try to compact another database it tends not to work until you get rid of the DB1 database

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compacting (Access 2003)

    I was wrong, a backend database indeed isn't compacted when the frontend is closed. This is because the backend database wasn't opened in the Access interface.

    You could write code to compact the backend from the frontend, but you must ensure that the code is run only when no connection to the backend is open, i.e. all forms and reports based directly or indirectly to tables in the backend are closed, and all recordsets (created in code) too. It's OK if an unbound form is open.

    The following code will try to compact DB2.mdb without a password, and DB3.mdb with a password "Secret". The name ZZZZ.mdb is an arbitrary name for the temporary database that is created during the process. It can be anything as long as it doesn't coincide with the name of an existing database.

    Sub CompactBackends()
    CompactIt "C:AccessDB2.mdb", "C:AccessZZZZ.mdb"
    CompactIt "C:AccessDB3.mdb", "C:AccessZZZZ.mdb", "Secret"
    End Sub

    Sub CompactIt(strSource As String, strDest As String, Optional strPwd As String)
    On Error GoTo ErrHandler
    Dim strExtra As String
    ' Check whether source exists
    If Dir(strSource) = "" Then
    MsgBox "Source database doesn't exist.", vbCritical
    Exit Sub
    End If
    ' Delete temp database if it exists
    If Not Dir(strDest) = "" Then
    Kill strDest
    End If
    ' String for password argument
    If Not strPwd = "" Then
    strExtra = ";pwd=" & strPwd
    End If
    ' Compact into temp database
    DBEngine.CompactDatabase strSource, strDest, , , strExtra
    ' Check if temp database was created
    If Dir(strDest) = "" Then
    MsgBox "Failed to compact.", vbCritical
    Else
    ' If so, delete the original database...
    Kill strSource
    ' And rename the temp one
    Name strDest As strSource
    End If
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting (Access 2003)

    Hi Hans,
    Thanks for all the trouble I have put you through. One last question. In DB1 after I execute the DoCmd.Quit, would DB1 be closed and could I then execute the code you gave me?
    Thanks again,
    Sarge

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compacting (Access 2003)

    Once you quit Access, code execution immediately ends, so you cannot use the code I posted *after* DoCmd.Quit.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compacting (Access 2003)

    Hi Hans,
    So putting this code just before the "DoCmd.Quit" , in the "Back To Windows", button "On Click", in the "Main Menu" form sounds like the right place to you? How about a "DoCmd.Close "tblMealsData"", right before I execute your code?
    Sarge

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compacting (Access 2003)

    Is tblMealsData open in the frontend? If so, yes, you must close it before running the code to compact the backend database. (I never let users open tables or queries directly, only forms and reports.)

    You should also make sure that all forms and reports bound to tables in the backend are closed. And if you have opened a DAO or ADO recordset on a backend table in VBA code, you must close the recordset too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •