Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Temporary Tables & Arrays

    W98, Office 97

    My office is migrating over to MSOffice from WordPerfect and I am converting a number of databases from Paradox to Access 97. Lets not even go into why we aren't using Office 2000.

    A common technique in Paradox is to use temp tables, since each table exists as an independent object, unlike Access. If you add an underscore before a table name, Paradox treats the table like a temp system table and handles your cleanup.

    Does Access have any construct like this? I build a number of temp tables to create complex reports and would like to do the same in Access. But this store everything in one file greatly hinders this. While testing a temp table scenario in Access, I quickly exceeded some memory or storage limits, until I compacted the database. Given what I've read on corruption when compacting, I would like to reserve this for more judicious usage on my main database. I am getting around this concern by writing the temp table out to another database designed for this purpose, but this seems asinine.

    Why do I need to have temp tables? Primarily because I build up report fields that are concatenated versions of data in the same field. For example, I have a name field that contains all the names of all people. Some are related to each other. When I print a report I generate several fields that contain concatenations of the names, depending on their relationships. So you might see "surname, name1 & name2" or "surname1, name1 & surname2, name2" in one field and "name1 age; name2 age" in another field. There is also quite a bit of formatting that gets added.

    Also, I tried a test doing all of this with an array and it locks the system. System PIII/800MHz-256MB ram, dedicated 3GB SCSI swap drive, 2x40GB IDE's. So plenty of horseys here. There seems to be some Access memory limit I am hitting. I am processing around 400K records. I just set a bookmark to a record, determine the person's type, use findfirst/next to loop through the other records to determine their status, make some decision based on the status & copy the data to the appropriate location, set a handled flag, and move on to the next record. Each record only gets handled once, beyond testing the handled flag. Once the array is build, I write it out to the temp table (in PDOX-doesn't work yet in Access).

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    Well, if you don't want to talk about why you are using Access 97 and not 2000, then how about why you are using access instead pf SQL server with that many records?

    However, to address your question, there is nothing that says that the temp tables have to be in the same files as the permanent tables. Just put the temp tables in a sepatate table and connect to the tables in that db. If you aren't already doing this to separate your data from your forms, queries, code, etc, then you need to setp back and take another look. I NEVER put my data in the same db file as the forms, queries, etc., and I seldom put all of the data in the same db file. Fixing a corruption problem is a major disaster when everything is in one file. If you put your temp stuff in a sepatate db file, then you can clean up by just replacing the temp db file with an empty template periodically.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    Thanks for your comments.

    Not my decision. I just play the cards I am dealt. Since we pull about 40% of our data from another client/server system, I would just like to use links, but alas, we are not allowed. We just get to use SQL to pull what we need at the start of each day.

    Once completed I was going to use the database splitter to separate the data and the front end. But I do not believe that this will change the underlying questions. If the system works as a whole then it should work independently. So if I get you correctly, you use a db for forms & queries, a db for vb code, and multiple dbs for data?

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    I would normally put the forms, Querries, and vb code all in one db file, and then split the data into however many db files seems to make sense. For example, I would keep names and addresses in one db file, but might put order that go with those names in a second, and the inventory to fill the orders in a third. I do split the forms, querries, and code for different systms into different db files. For example, even though they both might connect to the inventory data db file, I would have sepatate db files for an inventory management system and an order entry system.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    So back to the original post, the technique is to utilize temp tables in a separate db. I notice you commented that you delete and recreate the db object. I was going to have a temp db that held all of the temp db objects. Then I would just delete the data with a query and periodically compact the db. So if I follow your logic correctly, it would probably better to have individual dbs for each temp object I wish to create. Right now, one system I have creates 16 temp objects on which dozens of reports are based.

  6. #6
    Lounger
    Join Date
    Feb 2001
    Location
    TX, USA
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    What Legare means is to have a template temp.mdb which contains the table objects you wish to use. In your code, before linking to the temp tables, check the file size of your 'PRODUCTION' version of the temp.mdb. If it is over a size you feel comfortable with, just copy the 'TEMPLATE' version of the temp.mdb over the 'PRODUCTION' version. Then connect yout table links and proceed as usual.
    I actually use a variation of this technique with my distributed front-ends which can become bloated over time. My variation also checks for a NEWER version depending on whether I have added/modified/fixed anything to the front-end 'TEMPLATE'

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    Jim described pretty much what I do.
    Legare Coleman

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

    Re: Temporary Tables & Arrays

    One way to handle this is by writing to text files, which can be overwritten each time you run the routine, and then linking them to the database. I use this technique fairly often, but it requires code both to create the text files and to link them.

    You didn't explain why you would be creating all those variations on name and concatenating them together, but it sounds as if you may be doing it the hard way. Your reports sound more than complex, and I think you might want to rethink your approach to them since Access/VBA is a very different animal from Paradox.
    Charlotte

  9. #9
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    All names are stored in a single table along with what type of person they are and their relationship to an owner. The relationship can be spouse, co-owner, associate, child or anything else you want to define.

    The report format is fixed. In the report format the owner and spouse first names are concatenated, then any co-owners or associates fullnames are concatenated on one to two lines (space restrictions), then the child's name and ages are concatenated on another line along with the co-owners and associates children.

    You are correct, Access and Pdox are quite different. This is quite simple in PDOX. I first tried doing it on the fly with arrays and then writing out to a table but ran into memory problems on an K6-2 500MHz with 196M Ram, 1GB swap drive, Win98.

    So are you building up delimited text file,linking to it and basing a report off of this?

    Yes I am having to rethink quite a number of things, especially since Access's event model is not quite as robust as PDOX's.

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

    Re: Temporary Tables & Arrays

    Access and Paradox have different kinds of event models, but you're talking about object models and not events. Paradox is no more robust than Access, just different. I've worked with both, and my bias is toward Access.

    What you're trying to do may be simple in Paradox, but it isn't logical anywhere. Why are you creating a report by concatenating a bunch of names instead of using the individual data fields and allowing the report to do the work? The Access report generator is one of the best around, so make it do the work for you rather than just recreating what you did in Paradox.

    Maybe if you explain what the report is supposed to reveal, rather than just what it contains, you'll get more helpful answers.
    Charlotte

  11. #11
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    I realize you are trying to assist me, as well as I am trying to learn. The report format is logical. I do not know why you would state this. It may be unclear to you, however we can work to resolve this in order for both of us to more clearly understand and benefit. Perhaps it will assist someonelse also. I was talking events and not the object model. One example set is the inability of Access 97 to differentiate left and right mouse click events or single and double click events. Of which I have a large number of functionality attached to in PDOX systems. I apologize for this small defense of what I meant.

    As I stated, the report format is fixed. I do not have a choice here. All of the names are stored in a names table.

    salutation:first:middle:last:suffixersontype:relationship

    Pulling the info from this table is the issue, not the ultimate report format. Access's report generator will not pull this info and assemble it. It has to be done before the report is generated.

    The report format takes multiple permutations of the same format, eg:

    Smith, Joe & Susan
    221 Central
    Suite 101
    Chicago, IL 45125
    Bill 12; Sharon 8

    or

    Jones, Bill & Susan
    Thomas, Mike (Co-owner)
    221 Central
    Suite 101
    Chicago, IL 45125
    Bill 12; Sharon 8

    Mike would also have his own listing in addition to the Jones.
    Thomas, Mike (Co-owner)
    Jones, Bill & Susan (Owner)
    6000 North St
    Chicago, IL 45125

    These are only a few of the permutations. They actually fall into several classes based on person type and relationship.
    As you can see, one needs to prebuild these strings. Utilization of temporary tables or arrays is the best way to this type of work. Temp tables, while slower than arrays are more stable and relatively easy to work with.

  12. #12
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Temporary Tables & Arrays

    Actually Access does apply functionality to Click and Double Click events through property settings on Forms and can distinguish between mouse buttons using the Mouse Up Mouse Down events (see help). Building strings as you indicate in your example is also a simple matter in reports, e.g. [txtLastName]&" , "&[txtFirstName]&" and "&[txtSpouseName] etc. Creating a second instance of Mike Thomas can be handled in Sorting and Grouping of Reports. Having said that, you can also create the concatenation in the underlying query. I truly believe, Access will accomodate pretty much everything your asking for without the need to create temp tables. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> Good luck, and keep posting. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    PatriciaWilliams
    Guest

    Re: Temporary Tables & Arrays

    Hang in there, W.E.

    I, too, came from a very different background into Access. After the initial struggle, mostly with myself (just a _leetle_ bit on the foot-dragging side ("I know what I like and I like what I _know_" <g>)).

    In Clipper, I also would be building temporary tables often. I used arrays _all_ the time. I have been dissatisfied with Access' array capabilities which I almost _never_ use, and, although if you asked me now, I would still say that array capabilities in Access are nowhere near as powerful or flexible as what is available in Clipper, but I'll admit that there is a good chance that I am wrong (I've forgotten enough of my Clipper to go back and compare, now that I know more about Access). I learned early that I couldn't use them in the way that I had been, and finally went The Way of Access.

    I think you will find that queries in Access are tremendously flexible. You can build a query on one table, then turn around and use that query right back against the same table, and all _kinds_ of things. I think this is the direction that you will find most useful. Queries, in effect, can take the place of temporary tables. Of course, anything that is built and then deleted within an Access app does tend to bloat it, something that you wouldn't have faced with another type of database. (I've always thought that compacting is safe -- you just want to take precautions to have backups, in case.)

    So, there is just a little bit of a mindset change, but I think you will find, after you use Access for several months, that it will become an old friend to you, just like Paradox was.

    thx
    Pat

  14. #14
    geomouchet
    Guest

    Re: Temporary Tables & Arrays

    I have to agree whole-heartedly about using queries instead of temporary tables. For complicated logic and formatting, the iif, switch and format functions are very useful.

Posting Permissions

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