Create query in temporary DB

[Parker]

I am using a temporary db (created in code) to solve my mdb file size
growth problem. I have successfully created the tables I need in code,
used them, and deleted the temp DB on exit. However, I also have a
number of queries that are created on the fly. These are causing my
file size to bloat as well. I am trying to create these in the same way
I created the temp tables, but I cannot get it to work. Any help will
be immensely appreciated. Here is the code I am trying to use:
'CREATE QUERY IN TEMP DB
strQueryName = "searchQuery_Temp"

' Delete the link to the temp query if it exists
If queryExists(strQueryName) Then
CurrentDb.QueryDefs.Delete strQueryName
End If

' Create the temp query
Set qdfNew = dbsTemp.CreateQueryDef("")
With qdfNew
.Connect = "ODBC;DATABASE=" & strTempDatabase &
"DSN=Search_Table_Temp"
.SQL = "SELECT * from Search_Table_Temp;"
'Set rs = dbsTemp.OpenRecordset()
End With

dbsTemp.QueryDefs.Refresh

When using this code, I cannot get the query to appear in the temp DB
database window or in the current DB's window. Also, here is the code I
used to link the temp tables to my current DB. I cannot figure out how
to mofidiy this to work for the queries:
' Link to the Import tables in the temp MDB
Set tdfLinked = CurrentDb.CreateTableDef(strTableName)
tdfLinked.Connect = ";DATABASE=" & strTempDatabase
tdfLinked.SourceTableName = strTableName
CurrentDb.TableDefs.Append tdfLinked

 

[aaron.kempf]

MDB is crap use ADP. you dont have to worry about filesize bloat and
cheesy things like that

-aaron

 

[Douglas J Steele]

The reason you're not seeing the query is because you're not naming it.

Put a query name in:

Set qdfNew = dbsTemp.CreateQueryDef(strQueryName)

 

[Parker]

I put the name in there and now it shows up in my TempDB. However,
there are still two problems. First, if I open up the TempDB I can see
the query in the db window, but if I try to open it a "Select Data
Source" dialog box opens instead of the query result. What is going on
here? Second, I still cannot figure out how to connect it to the
CurrentDB so that I can see it in the CurrentDB's database window (like
I can with the temp tables). Thanks again for your help.

 

[Douglas J Steele]

I don't believe your Connect property is correct (you're missing a
semi-colon before the keyword DSN). However, what's strTempDatabase? If it's
a Jet database (i.e. an MDB), you can't use ODBC to connect to it from
Access)

You cannot create the equivalent of linked queries. In other words, there's
no way for you to see it in your front-end.

 

[aaron.kempf]

yeah all this is just a waste of time

ACCESS DATA PROJECTS are a much better solution

you can do temp queries on the shared tempdb if you want

just put a # symbol in front of the object name; and it will go away
when you close your session.

Since ADP keep the session open; it is a very nice stack