Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Database size bloating up continuously

Discussion in 'Information Technology' started by excel_hari@yahoo.com, Jul 28, 2006.

  1. excel_hari@yahoo.com

    excel_hari@yahoo.com
    Expand Collapse
    Guest

    Hi,

    I have created a database with a form having a single command button, a
    query doing some simple join/union and finally 2 code modules.

    The code for form is

    Option Compare Database
    Option Explicit

    Private Sub Command0_Click()

    Call LookupData

    End Sub

    The code for the first module is

    Option Compare Database
    Option Explicit


    Sub LookupData()

    'CurrentDb.Execute "Delete * from OSRM_Table "
    'CurrentDb.Execute "Delete * from ISSM_Table "

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    "OSRM_Table", TestIt("OSRM"), True, "Report 1!"

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    "ISSM_Table", TestIt("ISSM"), True, "ItemMaster!"

    DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
    BrowseFolder("Select a BASE Folder") _
    & "\OSRM_Table_SpecialData", True

    CurrentDb.Execute "Drop table OSRM_Table;"
    CurrentDb.Execute "Drop table ISSM_Table"

    DoCmd.Quit

    End Sub

    The code for second module is just a copy paste of Testit function from
    access mvps site and browse function from www.cpearson.com.

    The code for Query is

    SELECT "Special" As Category, OT.*
    FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    Cd]=IT.PIN
    WHERE (IT.[ISSD Special]="X")
    UNION ALL SELECT "Not Special", OT.*
    FROM OSRM_Table AS OT left JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    Cd]=IT.PIN WHERE (((IT.[ISSD Special]) Is Null));


    Basically, am using DB just to perform some lookup operation. If you
    notice the first module above, then you will see that am dropping the
    tables I created after my results get outputted. Inspite of dropping my
    tables my DB size keeps on growing up from 124 MB to 230 MB to finally
    336 MB right now. It seems each time I use this code (on the same set
    of excel files) my DB size also keeps on increasing (am doing some
    tweaking and fine tuning, so exporting the same excel files. How to
    overcome the same?

    Please guide me.

    regards,
    HP
    India
     
  2. Loading...

    Similar Threads Forum Date
    USA NSA has massive database of Americans' phone calls (USA Today 5.11.2006) Breaking News Jun 11, 2013
    Some database errors while navigating SPN forums Announcements Dec 29, 2004
    Nature Australia Plans Marine Reserve The Size Of India Breaking News Jun 25, 2012
    SciTech Japan Scientist Synthesizes Meat from Human Feces Breaking News Jun 18, 2011
    SciTech Kepler Space Telescrope Spots 5 Earth Size Planets in our Galaxy Breaking News Feb 3, 2011

  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Compact the database on a regular basis
    With newer versions you can tick off under tools /options to automatically
    perform this on Close

    HTH

    Pieter

    <excel_hari@yahoo.com> wrote in message
    news:1148895137.938310.107320@i40g2000cwc.googlegroups.com...
    > Hi,
    >
    > I have created a database with a form having a single command button, a
    > query doing some simple join/union and finally 2 code modules.
    >
    > The code for form is
    >
    > Option Compare Database
    > Option Explicit
    >
    > Private Sub Command0_Click()
    >
    > Call LookupData
    >
    > End Sub
    >
    > The code for the first module is
    >
    > Option Compare Database
    > Option Explicit
    >
    >
    > Sub LookupData()
    >
    > 'CurrentDb.Execute "Delete * from OSRM_Table "
    > 'CurrentDb.Execute "Delete * from ISSM_Table "
    >
    > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    > "OSRM_Table", TestIt("OSRM"), True, "Report 1!"
    >
    > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    > "ISSM_Table", TestIt("ISSM"), True, "ItemMaster!"
    >
    > DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
    > BrowseFolder("Select a BASE Folder") _
    > & "\OSRM_Table_SpecialData", True
    >
    > CurrentDb.Execute "Drop table OSRM_Table;"
    > CurrentDb.Execute "Drop table ISSM_Table"
    >
    > DoCmd.Quit
    >
    > End Sub
    >
    > The code for second module is just a copy paste of Testit function from
    > access mvps site and browse function from www.cpearson.com.
    >
    > The code for Query is
    >
    > SELECT "Special" As Category, OT.*
    > FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    > Cd]=IT.PIN
    > WHERE (IT.[ISSD Special]="X")
    > UNION ALL SELECT "Not Special", OT.*
    > FROM OSRM_Table AS OT left JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    > Cd]=IT.PIN WHERE (((IT.[ISSD Special]) Is Null));
    >
    >
    > Basically, am using DB just to perform some lookup operation. If you
    > notice the first module above, then you will see that am dropping the
    > tables I created after my results get outputted. Inspite of dropping my
    > tables my DB size keeps on growing up from 124 MB to 230 MB to finally
    > 336 MB right now. It seems each time I use this code (on the same set
    > of excel files) my DB size also keeps on increasing (am doing some
    > tweaking and fine tuning, so exporting the same excel files. How to
    > overcome the same?
    >
    > Please guide me.
    >
    > regards,
    > HP
    > India
    >
     
  4. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    One problem with Access is, it does not automatically release unused
    storage space when you delete data or objects; so, every time you import
    you add to the database size, while the opposite doesn't happen when you
    delete after outputting. This explains the constant bloat. To reclaim
    the unused storage space, you need to run a compaction. You can do that
    either manually (Tools > Database Utilities > Compact and Repair
    Database), or you can set the database to auto-compact every time you
    close it, by checking the box next to Tools > Options, tab General,
    Compact on Close.
    The latter assumes that (a) your Access version is 2000 or newer (this
    functionality was introduced in A2K), and (b) the database file in which
    the import and deletion happens is opened directly.
    As regards (b), in the case of a split database, you should store the
    temporary table(s) in the local front end, which will actually be
    compacted on closing; setting the Compact on Close on a back end that is
    accessed through a front end will not do the job, as the compaction
    fires only if the file itself is opened directly.

    HTH,
    Nikos
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    As the other have told you, compacting your database will reduce its size.

    However, is it really necessary to repeatedly import the data? Since you're
    strictly using the default settings when you import, can you not link to
    Excel? That way, you'll automatically get any changes that were made to the
    spreadsheet.

    Try changing the acImport in your two TransferSpreadsheet statements to
    acLink.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <excel_hari@yahoo.com> wrote in message
    news:1148895137.938310.107320@i40g2000cwc.googlegroups.com...
    > Hi,
    >
    > I have created a database with a form having a single command button, a
    > query doing some simple join/union and finally 2 code modules.
    >
    > The code for form is
    >
    > Option Compare Database
    > Option Explicit
    >
    > Private Sub Command0_Click()
    >
    > Call LookupData
    >
    > End Sub
    >
    > The code for the first module is
    >
    > Option Compare Database
    > Option Explicit
    >
    >
    > Sub LookupData()
    >
    > 'CurrentDb.Execute "Delete * from OSRM_Table "
    > 'CurrentDb.Execute "Delete * from ISSM_Table "
    >
    > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    > "OSRM_Table", TestIt("OSRM"), True, "Report 1!"
    >
    > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
    > "ISSM_Table", TestIt("ISSM"), True, "ItemMaster!"
    >
    > DoCmd.TransferSpreadsheet acExport, , "Specialized Query",
    > BrowseFolder("Select a BASE Folder") _
    > & "\OSRM_Table_SpecialData", True
    >
    > CurrentDb.Execute "Drop table OSRM_Table;"
    > CurrentDb.Execute "Drop table ISSM_Table"
    >
    > DoCmd.Quit
    >
    > End Sub
    >
    > The code for second module is just a copy paste of Testit function from
    > access mvps site and browse function from www.cpearson.com.
    >
    > The code for Query is
    >
    > SELECT "Special" As Category, OT.*
    > FROM OSRM_Table AS OT INNER JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    > Cd]=IT.PIN
    > WHERE (IT.[ISSD Special]="X")
    > UNION ALL SELECT "Not Special", OT.*
    > FROM OSRM_Table AS OT left JOIN ISSM_Table AS IT ON OT.[Prod Mfg SKU
    > Cd]=IT.PIN WHERE (((IT.[ISSD Special]) Is Null));
    >
    >
    > Basically, am using DB just to perform some lookup operation. If you
    > notice the first module above, then you will see that am dropping the
    > tables I created after my results get outputted. Inspite of dropping my
    > tables my DB size keeps on growing up from 124 MB to 230 MB to finally
    > 336 MB right now. It seems each time I use this code (on the same set
    > of excel files) my DB size also keeps on increasing (am doing some
    > tweaking and fine tuning, so exporting the same excel files. How to
    > overcome the same?
    >
    > Please guide me.
    >
    > regards,
    > HP
    > India
    >
     
  6. excel_hari@yahoo.com

    excel_hari@yahoo.com
    Expand Collapse
    Guest

    Pieter, Nikos and Douglas,

    Thanks a lot for the suggestion. I have enabled the option "Compact on
    Close" and now it is just 329 KB!!.

    My Access version is 2003 and the file is in 2000 format.

    Doug - Am repeatedly exporting same data files just to check whether
    the small tweaks am making in code would give me same results or not. I
    (actually my colleague is the one who needs this DB to be set up, am
    helping him so that I can use this stuff probably in similar scenarios
    in my future tasks) will be getting my regular excel data from some
    other source, so the name of the file, location etc would be different.

    Regards,
    HP
    India
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    If you can write code to import the spreadsheet, you can write code to link
    the same spreadsheet.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <excel_hari@yahoo.com> wrote in message
    news:1148903824.021616.307540@j33g2000cwa.googlegroups.com...
    > Pieter, Nikos and Douglas,
    >
    > Thanks a lot for the suggestion. I have enabled the option "Compact on
    > Close" and now it is just 329 KB!!.
    >
    > My Access version is 2003 and the file is in 2000 format.
    >
    > Doug - Am repeatedly exporting same data files just to check whether
    > the small tweaks am making in code would give me same results or not. I
    > (actually my colleague is the one who needs this DB to be set up, am
    > helping him so that I can use this stuff probably in similar scenarios
    > in my future tasks) will be getting my regular excel data from some
    > other source, so the name of the file, location etc would be different.
    >
    > Regards,
    > HP
    > India
    >
     
  8. excel_hari@yahoo.com

    excel_hari@yahoo.com
    Expand Collapse
    Guest

    Doug,

    Thanks for persisting with me.

    Actually I got the code for importing spreadhseet (Windows API dialog
    box) by copying from access MVPS website which has the TestIt function.
    Could you please let me know as to how is it possible to specify name
    change, location through code. Iam not actually in need of this here
    (because of nature of DB which is to perform just Lookups and then go
    to sleep) but I can probably use this in my future tasks.

    Regards,
    HP
    India
     
  9. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Not sure I understand what you're asking for.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <excel_hari@yahoo.com> wrote in message
    news:1148912060.471262.117810@g10g2000cwb.googlegroups.com...
    > Doug,
    >
    > Thanks for persisting with me.
    >
    > Actually I got the code for importing spreadhseet (Windows API dialog
    > box) by copying from access MVPS website which has the TestIt function.
    > Could you please let me know as to how is it possible to specify name
    > change, location through code. Iam not actually in need of this here
    > (because of nature of DB which is to perform just Lookups and then go
    > to sleep) but I can probably use this in my future tasks.
    >
    > Regards,
    > HP
    > India
    >
     
  10. excel_hari@yahoo.com

    excel_hari@yahoo.com
    Expand Collapse
    Guest

    Doug,

    You asked me to consider using the acLink instead of acImport. While am
    importing, the excel file could be in a different location and
    different file name as compared to last time. I thought you said that
    acLink can handle this, but I coudlnt understand as to how changes made
    in a newer set of file/location would reflect in the corresponding
    table in DB.

    Regards,
    HP
    India
     
  11. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    As I said, to link rather than import, you replace the one keyword in your
    TransferSpreadsheet statement.

    If you need to change where the linked spreadsheet is located, delete and
    relink is easiest.

    While this may not seem significantly different than what you're already
    doing, the fact that you're linking means that the data won't actually
    existing in your MDB. A linked table adds little to the size of the MDB.
    While deleting a linked table and relinking will cause some bloat
    (approximate 5 Kb), it'll be significantly better than what you've currently
    got.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <excel_hari@yahoo.com> wrote in message
    news:1148913960.971118.276380@g10g2000cwb.googlegroups.com...
    > Doug,
    >
    > You asked me to consider using the acLink instead of acImport. While am
    > importing, the excel file could be in a different location and
    > different file name as compared to last time. I thought you said that
    > acLink can handle this, but I coudlnt understand as to how changes made
    > in a newer set of file/location would reflect in the corresponding
    > table in DB.
    >
    > Regards,
    > HP
    > India
    >
     
  12. excel_hari@yahoo.com

    excel_hari@yahoo.com
    Expand Collapse
    Guest

    Doug,

    Thanks for your patience. I have finally understood your point.

    Regards,
    HP
    India
     

Share This Page