Recordset in Memory

[Michael]

I need to create temporary recordsets which get looped over and over again,
but I don't need to keep the data when I'm done. Is there any way to create
a recordset in memory, then releasing the memory when I'm done, without
having the data saved to a table?

At the end of my processes, my .mdb file gets huge and I have to compact it
each time. I'm using Access 2000.

Thanks,

Michael

 

[Brian Stoll via AccessMonster.com]

For Ex:

Private Sub someThing_someFunction()
Dim dbs As DAO.Database
Dim rstRecord As DAO.Recordset

CODE CODE CODE CODE CODE CODE

Exit_someThing_someFunction:
Set dbs = Nothing
rstRecord.Close
Exit Sub ' Or "Resume Next" if it's right before the "End Sub"
End Sub

 

[Michael]

I just realized I did not fully explain my problem (sorry). I need to create
a multi-record recordset where the recordset's fields are calculated from
code, then I need to create other recordsets which need to loop through the
initial recordset's records.

I'm currently saving the initial recordsets records to a temporary table and
then accessing that table with the other recordsets. My question is can I do
all this (in memory) without having to save the initial recordset to a table.

Sorry for the confusion.

Thanks,

Michael

 

[John Nurick]

How about an array?

I just realized I did not fully explain my problem (sorry). I need to create
a multi-record recordset where the recordset's fields are calculated from
code, then I need to create other recordsets which need to loop through the
initial recordset's records.

I'm currently saving the initial recordsets records to a temporary table and
then accessing that table with the other recordsets. My question is can I do
all this (in memory) without having to save the initial recordset to a table.

Sorry for the confusion.

Thanks,

Michael

 

[Brian Stoll via AccessMonster.com]

I haven't tried it, but can't you delete a table from code? When you're
done using it, IE: in the Exit_someThing_someFunction, you could delete the
form you created...

Sorry, I don't know of a way to do it all in memory - unless you save all
the fields in the recordset as a variable, then you aren't creating a new
table, just a bunch of variables.

 

[Michael]

Since I run these procedures on a regular basis, I just have temporary tables
set up for this purpose and delete all the records each time I need to, but
the database gets bloated. For example, my .mdb file is usually 11MB, but
after running this process once, it grew to 123MB - eventhough all the
temporary records had been deleted. I have to compact the .mdb each time to
get it back to it's normal 11MB size.

I was just wondering if there's a way to populate a recordset in memory, use
it, and then remove it from memory when done without having to store it to a
table first. I don't think an array would work because the number of records
I need for the temporary recordset is different each time.

Thanks,

Michael

 

[Douglas J. Steele]

Consider putting your temporary tables in a temporary database.

Tony Toews shows one way of doing this at
http://www.granite.ab.ca/access/temptables.htm

 

[Michael]

So, I gather, there is no way to accomplish what I want. Is that correct?

Michael

 

[Ken Snell [MVP]]

ADO recordsets can be disconnected (meaning they are stored in "memory"...
perhaps you might find them useful for your purposes?

 

[Marshall Barton]

I wouldn't go so far as to say "no way", but you are asking
for a very specific way to accomplish something when there
are other ways to get the job done.

Using a temporary database is a simple, easy to implement
way that I would use in this kind of situation.

If you were using ADO, then a disconnected recordset would
be almost exactly what you're looking for.

DAO doesn't have disconnected recordsets, but, you could
start a transaction, create a table, manipulate its data,
and then, when you're done with it, use Rollback to throw it
away.

 

[John Spencer (MVP)]

If you are using standard Access MDB then I know of no way to do what you have
asked other than using an array to hold the information or using a temporary
database and tables in that temp db. Both ideas have been suggested to you already.

SO, your statement is apparently correct.

So, I gather, there is no way to accomplish what I want. Is that correct?

Michael

 

[Michael]

Thanks. I've never used the Transaction functionality but sounds like it
would work for me. What is the basic concept behind it so I can understand
what I'm reading about in the Help.

Thanks,

Michael

I wouldn't go so far as to say "no way", but you are asking
for a very specific way to accomplish something when there
are other ways to get the job done.

Using a temporary database is a simple, easy to implement
way that I would use in this kind of situation.

If you were using ADO, then a disconnected recordset would
be almost exactly what you're looking for.

DAO doesn't have disconnected recordsets, but, you could
start a transaction, create a table, manipulate its data,
and then, when you're done with it, use Rollback to throw it
away.
--
Marsh
MVP [MS Access]

So, I gather, there is no way to accomplish what I want. Is that correct?

 

[Michael]

Thanks. How do I work with ADO recordsets when doing VBA in Access? What is
the difference between ADO and normal recordsets?

Thanks,

Michael

 

[Marshall Barton]

Tranactions are a way to make a series of data operations
atomic. I.e. they are all committed or, if something
prevents one operation from running successfully, then you
can roll all of them back so none of them actually are
stored in any table.

Read up on BeginTrans, CommitTrans and Rollback in Help,
then, if there's something you need further assistance with,
post a specific question to a new thread.

 

[David C. Holley]

Depending on what you're trying to do, you might do something that uses
the .RecordsetClone property. I did a nifty little trick that allows me
to basically add records two a master table and then attach them as line
items to a reservation. Think of it as creating a new purchase order and
having the ability to add new products on the fly to tblProducts and
then when the form closes adding the entered products to the purchase order.

So, I gather, there is no way to accomplish what I want. Is that correct?

Michael

:

 

[Ken Snell [MVP]]

ADO has a completely different setup and syntax from DAO (which I assume is
what you meant by "normal recordset"). You can find lots of specific
information in Help files, and in various books about ACCESS. ADO can be
handled via VBA similarly to what you do for DAO.

--

Ken Snell
<MS ACCESS MVP>

 

[Michael]

Can ADO and DAO code be intermixed in one .mdb?

Thanks,

Michael

 

[Alex White MCDBA MCSE]

Yes, you need references to both libraries, can get confusing so get your
naming convention sorted

e.g.

dim daoTable1 as new dao.recordset
dim adoTable2 as new adodb.recordset



--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[David C. Holley]

Yes. ADO & DAO are two different ways to manipulate data, however as
they are different you can't start manipulating data using 1 and then
switch over to the other without using some sort of intermediary. Think
of it as if start out driving from your house to the airport using a
car. If you want to switch to a SUV, you have to stop the car unload
everything, load up the SUV and then take off. You can't magically
switch from the car to the SUV driving 70 mph down the highway without
stopping.

David H

 

[Michael]

Ok, thanks.

But I can use ADO on some stuff and DAO on others, right?

Thanks,

Michael