Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Temporary tables...? (2000/9.0.3821 SR1)

    From time to time, I see references to temporary tables in this forum. Understandably, the discussions proceed as if everybody knows what they're talking about. I have a hunch that I might want to use temporary tables in a project I'm working on, but I don't have the first clue how to create such a thing nor why I should or should not. I tried searching the Access Help file, the index in Ms. Feddema's "Access 2002 Inside & Out" book, and the index in Gary Buszek's "Instant Access Databases." So far, I haven't found the two words, "temporary" and "table" appear in sequence. I'm not taking shots at any authors, but I would like to learn about temporary tables. Can anybody offer a short primer or even just point me to a good source of information. Thanks!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    When I use the term "temporary table", I mean a regular Access table to which I write records "temporarily " in order to use them in another object. After I am done producing the dependent object (maybe a query or a report), I delete the records in the table, so it is empty for the next time I want to use it again.

    One example of this is a series of reports that I run against a data range. The actual table is very large and the reports will take a long, long time. If I write the records within the desired data range to a temporary table, and use the temporary table for the dependent queries and reports, all moves along quickly.

    Hope that sheds a bit of light - others may know more ...

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    There are essentially two kinds of temporary tables, those such as Thomas describes and those that are created, used and then destroyed.

    You can create an "on-the-fly" temporary table using a Make-table query, allowing it to overwrite any previous instance of the same table, or you can create a permanent table and use delete and append queries to clear its contents and repopulate it. Both kinds of temporary tables lead to database bloat, so you need to be aware of that and plan on compacting the database fairly often if you use one of these approaches.

    If you want to base queries or reports on temporary tables, then you may want to use permanent "temporary" tables. If you're using SQL Server tables, the term has still another meaning, by the way. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    Thank you, Thomas and Charlotte, for that information.

    'Nudder question: Charlotte, you allude to problems with "database bloat" caused by temporary tables. If I create a "permanent" temp table, and empty it-- manually or otherwise--after I've finished with it, how can I bloat my database? Is there stuff going on in the background...or is bloat simply the result of our human tendency to leave stuff behind? Like the way I let my inbox pile up with 200 or more messages. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    It's the tendency of Microsoft programmers to leave stuff behind - whether they're human, I'll leave up to you.

    When you delete records from a table, Access doesn't release the space they took up. When you add new records, Access allocates new space for them, instead of reclaiming the space of the old records. As a consequence, the size of the database increases all the time.

    That is one of the reasons why you need to compact your databases from time to time (or have them compact on exit).

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    A-ha! Thank you very much!

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

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    Access2000 seems particularly bad about bloating with frequent adds/deletions to a table. My advice is to create a separate database and create your temp tables there, then link them to your frontend.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Temporary tables...? (2000/9.0.3821 SR1)

    Thomas, Charlotte, Hans, Mark: Thank you all. You'll cleared this one up for me!

Posting Permissions

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