Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    How To? Create temp recordsets (Acc 97 sr2 on 95b)

    I want to create a temp recordset to hold short term data. After completion of all input, the data will be manipulated and the results written out to 2 tables. The original recordset can then be deleted.

    I realise I could do this by writing to a table and then deleting the table contents, but I hope to learn something new on the way.

    Can someone write me the basic syntax for setting up the temp recordset. Writing to it and manipulating the data I think I can do.

    Thanks in advance
    "Heading for the deep end"

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How To? Create temp recordsets (Acc 97 sr2 on 95b)

    A recordset is based upon a query (which is based upon a query or table) and/or a table.
    The table is the only object which can hold data, a recordset is generally a reference to a table.
    So the table is the lowest common denominator.

    Your comment:
    <<I realise I could do this by writing to a table and then deleting the table contents>>
    is valid and its the only way to do it (I think), unless somebody can correct me.

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

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

    Re: How To? Create temp recordsets (Acc 97 sr2 on 95b)

    ADO allows you to create a recordset without a table behind it, but you won't be able to update any tables from it. ADO also allows you to "pesist" a recordset to a file and then work with it before syncing it back to its source table, but it is somewhat unidirectional--you can't make changes in both the table and the persisted recordset and sync them, you can only make changes in the persisted recordset if you want them written back to the table. Even then, you would still need a table of the appropriate shape for the recordset to be written to and from because you can't just query the persisted recordset the way you can a table or query. You also can't manipulate the data in any way that changes its structure because the persisted recordset has to match the table structure it is written to and from. This is easy in Access 2000 and 2002, but requires a lot of coding around things that don't exist in Access 97, like the CurrentProject object. It also requires you to handle all the coding for unbound forms because forms bound to an ADO recordset are not updateable in an MDB. I actually have a demo of using persisted recordsets, and I have converted it loosely to A97 (I didn't even try to find memory leaks,etc.), but it is too large to post here.

    Perhaps if you explain what your purpose is, someone can come up with a viable alternative for 97. A temporary table will do what you want, and I see no reason for you to avoid using one. Trying to graft ADO onto A97 is a frustrating exercise at best. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How To? Create temp recordsets (Acc 97 sr2 on 95b)

    You blow me away Charlotte, your knowledge is truly amazing. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    I would like a copy of that A97 database, if it's not too much trouble.
    Would you send it to my email address in my profile.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: How To? Create temp recordsets (Acc 97 sr2 on 95b)

    I currently enter timesheet data into a table. Part of the data is then written out to another table and some values calculated while transferring, using an append query. Another subset is written to another table in the same way. The initial table is then cleared.

    The data is entered into the table because day start and finish times per employee were taken from their first and last job.

    If a table in memory could be created to hold the data until input was complete, the data then split as required, without being too complex, I was interested in how as part of my learning JET coding.

    From your post, it seems that the effort (not to mention time) is not worth the outcome and the coding required way above my current level. As you pointed out, there is nothing wrong with what I am currently doing. I was just curious. The "Keep it simple" adage rings true
    "Heading for the deep end"

Posting Permissions

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