create a temp table

J

JohnLute

I've got a very complex query behind a report that's more than likely the
cause of the dreaded "Can't Open Any More Databases" error message. I've read
all over the internet about solutions to this and have come across the idea
of creating a temporary table and then running the report based on the
temporary table. It seems to me that this is the path of least resistance for
my particular problem.

Can anyone please give me an idea on how to go about doing this?

Thanks in advance!
 
J

Jeff Boyce

John

Create the query that returns the fields you need.

If you MUST create a temp table, change the query from above into a Make
Table query ... or

Create a table with the structure you want and change the query into an
Append query.

If you use the latter approach, your report can be based on a query that's
based on the table that's always there, rather than on one that magically
appears (Make Table).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

Tony Toews [MVP]

JohnLute said:
I've got a very complex query behind a report that's more than likely the
cause of the dreaded "Can't Open Any More Databases" error message. I've read
all over the internet about solutions to this and have come across the idea
of creating a temporary table and then running the report based on the
temporary table. It seems to me that this is the path of least resistance for
my particular problem.
Agreed

Can anyone please give me an idea on how to go about doing this?
Click to expand...

See the TempTables.MDB page at my website which illustrates how to use
a temporary MDB in your app.
http://www.granite.ab.ca/access/temptables.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
J

JohnLute

Thanks, Jeff!
Create the query that returns the fields you need.
Click to expand...

Done. And it's a WHOPPER!
If you MUST create a temp table, change the query from above into a Make
Table query ... or
Click to expand...

I "must". No other option as far as I can tell.
Create a table with the structure you want and change the query into an
Append query.

If you use the latter approach, your report can be based on a query that's
based on the table that's always there, rather than on one that magically
appears (Make Table).
Click to expand...

I think I like the latter better however when data changes then wouldn't
appending make it more difficult to update the table?

Let's say I used the Make Table method. How do I fire it up? I need to run
the query first in order to make the table, no? If so, what's the best way to
make all of this happen?

Thanks!
 
J

JohnLute

Thanks, Tony. I've seen your other responses regarding this solution which is
a bit mind boggling at the moment! I'm going to need some time to digest it!
 
Top