Is it possible to access a temporary query from a second query?

[Brad]

In order to avoid modifying a “permanent†QueryDef, I have created a
temporary QueryDef. Now I would like to be able to access this temporary
query via a second permanent “make table†query.

Is this possible with Access 2007?

I have been doing quite a bit of reading and experimenting with no luck. My
guess is that this is easy to do but I am missing something.

Thanks for your help,


Brad

 

[Ken Snell]

How are you naming the temporary querydef? Did you save it as part of your
code? You can save it, use it, then delete it.

 

[Brad]

Ken,

Thanks for your help. Based on the advice of others, I am trying to avoid
changing QueryDefs at execution time and I am trying to avoid adding and
deleting the query. It is my understanding that such practices are
discouraged because they may cause corruption in the long run.

It is also my understanding that if you give a query a name, it is a
permanent query. Perhaps I am missing something here.

I was advised to use a temporary query (no name). I have this temp query
working, and now I would like to access this temporary query from an existing
“Make Table Queryâ€.

Thanks again,
Brad

 

[Brad]

Ken,

Thanks for your help. Based on the advice of others, I am trying to avoid
changing QueryDefs at execution time and I am trying to avoid adding and
deleting the query. It is my understanding that such practices are
discouraged because they may cause corruption in the long run.

It is also my understanding that if you give a query a name, it is a
permanent query. Perhaps I am missing something here.

I was advised to use a temporary query (no name). I have this temp query
working, and now I would like to access this temporary query from an existing
“Make Table Queryâ€.

Thanks again,
Brad

 

[Duane Hookom]

I write a lot of code to change the SQL property of saved queries with no
problems. Each user should have their own copy of the front end.

 

[Brad]

Duane,

Thanks for your feedback. Here is our situation. We are just starting to
use Microsoft Access. Others have warned us about the problems with
corruption.

Our first little application does modify a QueryDef. After only a few days
in Production, we experienced what appears to be some sort of corruption. We
are using a split database and each user has their own front end. The error
message that indicates possible corruption does not give us much to go on.

A number of people who post information on this forum seem to think that
modifying QueryDefs can cause problems. Because we are fairly new to using
Access, we are unsure how to proceed. Using a temporary query sounds like a
safer method, but we really don’t know if the problem that we are having is
caused by changing the QueryDef or not.

Thanks again,
Brad

 

[Duane Hookom]

It's been a long time since I have experienced any corruption but your
experience may vary. There are some good pages on the web regarding Access
corruption. Much corruption is caused by bad network connections.

 

[Clifford Bass]

Hi Brad,

Two thoughts. One, just make your temporary query the make-table
query, all in one query. Or am I missing something? Two, instead of
creating temporary queries, just use the SQL directly in code. Something
like:

Dim strSQL As String

' Stuff to construct your make table SQL
strSQL = "insert into table1 xyz (col1, col2) " & _
"select col3, col4 from table2 where col5 = 123"
' Execute the SQL
CurrentDB.Execute strSQL

Clifford Bass

 

[Ken Snell]

Yes, giving a query a name makes it a "permanent" query -- permanent until
you delete it, of course.

The reason for giving the query a name is to provide complete control over
what you do with that query. I've steered clear of temporary queries because
I'm never sure how I would create more than one a time and have them be
named in such ways that I could use the one I want. (May not be an issue for
others, but ...)

I've never had problem with corruption caused by using QueryDef and by
creating/deleting queries during execution. As Duane said, corruption
usually results more from dropped network connection while
updating/creating/deleting data.