Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Back End bloat (2003/2000)

    (Edited by HansV to make provide link to post - see <!help=19>Help 19<!/help>)

    Bit of background first; we have a dept that has been using a spreadsheet to record test results. Most of the data they're inputting is on one of our AS/400 servers, so they're duplicating data input!

    So a DB seemed the best idea (MS query for the spreadsheet isn't robust enough), however this data is stored in 12 different, but related, tables! What with network & server performance concerns, it was decided to import the relevant data into an Access BE & use that for the test records. The BE only has these tables & is purely to provide a repository from which the FE can relate to. As for users, the maximum number would be about 5, so that shouldn't be an issue.

    I've made as many of the pass-through queries dynamic (i.e. looking for data after the last record in the BE DB) as possible & we're using append queries to update these tables.

    The problem comes after the update, the back end expands exponentially; from 45MB to 1.7GB!!! So I'm running a compact & repair daily.

    So my questions are;
    1) What can I do to prevent the bloat?
    2) If this cannot be prevented, what code should I use to compact the BE, after the update.

    TIA

    PS I have read the previous thread; <post#=503999>post 503999</post#>

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

    Re: Back End bloat (2003/2000)

    In the thread you mention, we didn't find an explanation or solution, sadly.

    You can compact the current database using the code from Compact the Current Database from Code.

    Or you can use the CompactDatabase method in the DAO library to compact an external database. This method requires that you provide different names for the original database and the compacted database. After compacting, you can delete the original database and rename the compacted one. Take care to check that the compacted database has actually been created. If it failed, and if you delete the original database, you won't have a database left!

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    Ah, that explains why I didn't find a solution <img src=/S/sad.gif border=0 alt=sad width=15 height=15>.

    Could you give me some sample code for the CompactDatabase (DAO) method?

    I'm a little confused as how this can work when the BE is, in effect, open via the FE. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Back End bloat (2003/2000)

    You can *not* compact the backend while it is in use (from the frontend or otherwise). You can use a maintenance database that does not have a link to the backend to run the code, and you must wait until all users have left before doing so.

    The following sample code is based on the example in the DAO help. It will try to compact the database BE.mdb in serversharefolder, using BETemp.mdb in the same folder as intermediary step.

    Sub CompactBE()
    Const strDatabase = "serversharefolderBE.mdb
    Const strTempDb = "serversharefolderBETemp.mdb

    On Error GoTo ErrHandler

    ' Make sure temp database doesn't exist
    If Not Dir(strTempDb) = "" Then
    Kill strTempDb
    End If

    'Compact to temp database
    DBEngine.CompactDatabase strDatabase, strTempDb

    ' Check whether temp database has been created
    If Dir(strTempDb) = "" Then
    MsgBox "Could not create compacted database", vbExclamation
    Exit Sub
    End If

    ' Delete original database
    Kill strDatabase
    ' Rename temp database
    Name strTempDb As strDatabase
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    Cracking mate, all done & working.

    Cheers <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    I just got to wondering (playing devils advocate), could the temp DB be left without an original?

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

    Re: Back End bloat (2003/2000)

    The CompactDatabase method won't delete the original database, it leaves the original database alone. If it succeeds, it creates a new compacted database, and if it fails, it doesn't create anything.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Back End bloat (2003/2000)

    Just a question about possible cause of bloat. When you are doing these append queries, are you always appending all the data, or are you relying on primary or unique keys to eliminate duplicates?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    About 25% of the updates are relying on the PKs to eliminate duplicates, as jobs are not always done in sequence. The others use Dmax on the relevant table/field to dynamically create the pass-through queries.

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Back End bloat (2003/2000)

    I'd construct my append queries to check for duplicates so you are only appending new records. Do this by doing a LEFT OUTER JOIN to the destination table, joining on the PK fields. If the PK field on the destination table IS NULL, then a record doesn't already exist.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    I like that. I'll build that in.

    BTW Hans, the code works a dream, but forward thinking a bit, is there any way that a message box could show when the process is complete, or even open the FE?

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

    Re: Back End bloat (2003/2000)

    Yes, you can add a MsgBox instruction near the end.
    You can open another database in the very last instruction before Exit Sub:

    OpenCurrentDatabase "C:ThisThatFE.mdb"

    Because you're opening another database, any code after this instruction will be ignored.

  13. #13
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    Well I've put that in, but I'd like the utility DB to close at this point. Here's what I've used;

    strOpenDB = MsgBox("Do you want to open the database now?" _
    & vbCr & "If no, this utility will close.", _
    buttons:=vbYesNo, _
    title:="Finish")
    If strOpenDB = vbYes Then
    Application.FollowHyperlink "O:TeamstechTestHouse DBTestRecordsV3.3.mdb"
    Else
    **************
    End If


    Now I'm guessing that if the code opens another DB, the freshly opened DB has the focus & the code from the utility DB is effectively halted. So what would be the optimal way of going about this? Have some start up code close the specified DB, perhaps?

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

    Re: Back End bloat (2003/2000)

    Since you open the front end database in another instance of Access, you can simply quit the current instance after the FollowHyperlink line:

    Application.Quit

  15. #15
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back End bloat (2003/2000)

    As easy as that. Cheers mate.

Posting Permissions

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