Q: Editing an "uneditable" query? Or- creating a recordset in RAM?

  • Thread starter Thread starter BrainlordMesomorph
  • Start date Start date
B

BrainlordMesomorph

I have a (continuous) form that is based on a query that is based on
query that is based on a query. (yes, 3 queries deep) Needless to say,
the records are not editable. I would like my users to able to edit
them.

Now I am very temped to turn that third query into a "MakeTable"
query, to make a *temporary* table and then base the form on that.
Then I would have an editable form, and I could capture any changes
the user makes and send them (via ADO) back to the original data
tables.

I say temped, because I know from experience that doing that will turn
my (already slightly chubby) app into a giant harddrive pig! (I've
had apps freeze because MDB files became bigger than 2 GB!!) Yes, I
know I can compress, and I do, but it's still a harddrive pig while it
runs. Not to mention being slow with all the reading and writing.

So, I know how to set an ADO recordset to a table or a query.

My question is: can I create a *editable* recordset in RAM, instead of
a temporary table hard drive? (like an array or something?) And then
could I use that as the basis of my form?

Or are Temp Tables really the best (only) way to do this?

Aha tia
blm
 
Brian

I believe you are gonna' be stuck with the Temp Table method. Even if you
could create that recordset in memory using ADO, you cant bind an access
form to it.

You may wish to go back and restructure your database design so it can be
queried without all of the steps you are currently using. Access usually
has good reason for not allowing updates on certain kinds of queries. If it
is impossible to restructure or simplify your query to get the required
results, then a temp table might be the only option available to you.

If you are forced to the Temp table route, I would recommend however that
you use a pair of delete and append queries on an existing temp table rather
then creating and destroying bunches of em'. You can create the temp table
in a separate BackEnd database and link it to your FrontEnd in effort to
reduce the effects of database bloat in the critical database files. You
could at your leisure compact the Temp Table database whenever it started to
get to be an unruly size without affecting the main application.

Remember you will be responsible to writ all of the code necessary to get
the temp data back into your current datastructure. It may not be neat and
clean, but it will work.

Ron W
 
BrainlordMesomorph said:
I have a (continuous) form that is based on a query that is based on
query that is based on a query. (yes, 3 queries deep) Needless to say,
the records are not editable. I would like my users to able to edit
them.

Now I am very temped to turn that third query into a "MakeTable"
query, to make a *temporary* table and then base the form on that.
Then I would have an editable form, and I could capture any changes
the user makes and send them (via ADO) back to the original data
tables.

I say temped, because I know from experience that doing that will turn
my (already slightly chubby) app into a giant harddrive pig! (I've
had apps freeze because MDB files became bigger than 2 GB!!) Yes, I
know I can compress, and I do, but it's still a harddrive pig while it
runs. Not to mention being slow with all the reading and writing.

So, I know how to set an ADO recordset to a table or a query.

My question is: can I create a *editable* recordset in RAM, instead of
a temporary table hard drive? (like an array or something?) And then
could I use that as the basis of my form?

Or are Temp Tables really the best (only) way to do this?

Aha tia
blm
Click to expand...

The fact that you query is based on a query that is based on a query
does not, in itself, force it to be nonupdatable. I have plenty of
updatable queries that are nested like that. There are very specific
query designs that force queries to be nonupdatable. Maybe you can
rewrite your query so that it is updatable. If you haven't already
looked at it, see the help topic, "When can I update data from a query?"

Many of the cases where a query is nonupdatable are ones where it is
impossible for Access to tell *which* specific record is supposed to be
updated. If this is the case with your query, you may be out of luck --
if you also can't logically identify which record is to be updated. On
the other hand, if you can tell which source record is supposed to be
updated by a change to a result record, then you can probably find a way
to tell Access that in the query design.

If you can't find a way to make the query updatable, then turning it
into a make-table query and basing your form on the temporary table you
have made is probably the best approach. You can get around the problem
of database bloating that this approach tends to cause by actually
creating the table in a separate, external, temporary .mdb file that you
create on the fly for this very purpose. Tony Toews has an article
about this on his web site here:

http://www.granite.ab.ca/access/temptables.htm

I wrote a simple class object to encapsulate the functionality that he
describes there and manage temporary tables in a temporary .mdb file.
If you're interested, I'll e-mail it to you.
 
Back
Top