Ways to keep database size small

Discussion in 'Microsoft Access VBA Modules' started by gci1000, Oct 21, 2008.

  1. gci1000

    gci1000 Guest

    Instead of constantly having to compact database, what is one way to use
    tables, or temp tables differently in such a case that the access database
    will not grow up so far. Right now, I have "delete * from table", then later
    .... "insert into table from ...". Any suggestions ?
    Tanks,
    --
    James
     
    gci1000, Oct 21, 2008
    #1
    1. Advertisements

  2. gci1000

    John Spencer Guest

    Use a temporary Dateabase and populate the tables there.

    See Tony Toews website
    http://www.granite.ab.ca/access/temptables.htm
    for an example

    I use a slight modification of Tony's methodology, where I build the table
    design in my database with no records in the table. I then name the table
    with an _SRC appended to the end. Then I copy the table over into the
    temporary database and drop the appended _SRC when I do so. Then I link to the
    table in the temp database.

    John Spencer
    Access MVP 2002-2005, 2007-2008
    The Hilltop Institute
    University of Maryland Baltimore County

    gci1000 wrote:
    > Instead of constantly having to compact database, what is one way to use
    > tables, or temp tables differently in such a case that the access database
    > will not grow up so far. Right now, I have "delete * from table", then later
    > ... "insert into table from ...". Any suggestions ?
    > Tanks,
     
    John Spencer, Oct 21, 2008
    #2
    1. Advertisements

  3. On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
    <> wrote:

    >Instead of constantly having to compact database, what is one way to use
    >tables, or temp tables differently in such a case that the access database
    >will not grow up so far. Right now, I have "delete * from table", then later
    >... "insert into table from ...". Any suggestions ?
    >Tanks,


    If you really need to routinely empty and refill a table (hint: you probably
    DON'T, since a SELECT query may get you the same result), consider putting the
    temp table into a temporary .mdb file, created as needed, destroyed when
    finished. Tony Toews has an example at
    http://www.granite.ab.ca/access/temptables.htm

    Again; note that if you're creating a table just so that you can base a report
    or an export on it, you're wasting time and effort; you can base a report or
    an export on a Query without creating a table.
    --

    John W. Vinson [MVP]
     
    John W. Vinson, Oct 21, 2008
    #3
  4. gci1000

    dch3 Guest

    Going with this method, you can write a VBScript that automatically deletes
    the temporary table, replaces it and then starts Access. Its then just a
    matter of teaching the users to use the script to start the database -
    they'll be none the wiser. We do something similar to ensure that all users
    have a current copy of the front end on their local machines.

    "John W. Vinson" wrote:

    > On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
    > <> wrote:
    >
    > >Instead of constantly having to compact database, what is one way to use
    > >tables, or temp tables differently in such a case that the access database
    > >will not grow up so far. Right now, I have "delete * from table", then later
    > >... "insert into table from ...". Any suggestions ?
    > >Tanks,

    >
    > If you really need to routinely empty and refill a table (hint: you probably
    > DON'T, since a SELECT query may get you the same result), consider putting the
    > temp table into a temporary .mdb file, created as needed, destroyed when
    > finished. Tony Toews has an example at
    > http://www.granite.ab.ca/access/temptables.htm
    >
    > Again; note that if you're creating a table just so that you can base a report
    > or an export on it, you're wasting time and effort; you can base a report or
    > an export on a Query without creating a table.
    > --
    >
    > John W. Vinson [MVP]
    >
     
    dch3, Oct 21, 2008
    #4
  5. You don't really need VBScript.

    As long as no connections are open to the temporary database, you can delete
    it from the front end using the Kill statement.

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


    "dch3" <> wrote in message
    news:...
    > Going with this method, you can write a VBScript that automatically
    > deletes
    > the temporary table, replaces it and then starts Access. Its then just a
    > matter of teaching the users to use the script to start the database -
    > they'll be none the wiser. We do something similar to ensure that all
    > users
    > have a current copy of the front end on their local machines.
    >
    > "John W. Vinson" wrote:
    >
    >> On Tue, 21 Oct 2008 08:37:02 -0700, gci1000
    >> <> wrote:
    >>
    >> >Instead of constantly having to compact database, what is one way to use
    >> >tables, or temp tables differently in such a case that the access
    >> >database
    >> >will not grow up so far. Right now, I have "delete * from table", then
    >> >later
    >> >... "insert into table from ...". Any suggestions ?
    >> >Tanks,

    >>
    >> If you really need to routinely empty and refill a table (hint: you
    >> probably
    >> DON'T, since a SELECT query may get you the same result), consider
    >> putting the
    >> temp table into a temporary .mdb file, created as needed, destroyed when
    >> finished. Tony Toews has an example at
    >> http://www.granite.ab.ca/access/temptables.htm
    >>
    >> Again; note that if you're creating a table just so that you can base a
    >> report
    >> or an export on it, you're wasting time and effort; you can base a report
    >> or
    >> an export on a Query without creating a table.
    >> --
    >>
    >> John W. Vinson [MVP]
    >>
     
    Douglas J. Steele, Oct 21, 2008
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alp
    Replies:
    8
    Views:
    178
  2. Lars Pedersen

    Changing size of microsoft access to reflect form's size?

    Lars Pedersen, Mar 21, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    1,333
    Peter's Software
    Mar 25, 2005
  3. Guest

    Access 2003 Keep database window maximized

    Guest, Dec 8, 2005, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    283
    Guest
    Dec 8, 2005
  4. Guest

    keep having to debug and compile access database

    Guest, Jul 31, 2006, in forum: Microsoft Access VBA Modules
    Replies:
    1
    Views:
    490
    Allen Browne
    Aug 1, 2006
  5. Ken Warthen

    Different ways of declaring listview or treeview control

    Ken Warthen, Sep 9, 2008, in forum: Microsoft Access VBA Modules
    Replies:
    2
    Views:
    538
    Ken Warthen
    Sep 9, 2008
Loading...

Share This Page