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

Topic

New Reply

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

Reply | Quote

Replies

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:
<>
is valid and its the only way to do it (I think), unless somebody can correct me.

Pat

Reply | Quote

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.

Reply | Quote

You blow me away Charlotte, your knowledge is truly amazing.
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

Reply | Quote

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

Reply | Quote