Make-Table Qry in backend database

[Bob Bonta]

In the given query statement:

Select ... From ... Into tblTemp Where ...

can tblTemp be created in a backend database rather than
the frontend database in which the query is actually being
processed?

Any thoughts or recommendations would be appreciated.

RSBonta

 

[Guest]

Syntax :

SELECT Table1.* INTO test IN 'C:\test.mdb'
FROM Table1;

- Raoul

 

[Guest]

JaRa's idea will work, but that will assume the mdb file is in the correct
place. I might suggest a different approach.
Create the table with the layout you need in the backend.
Link to the table in the frontend.
Instead of a make table query, delete all the records in the table, then use
an append table to load the new data.

 

[Bob Bonta]

Thank you - but it MUST be a make-table query. The
structure changes from one query to the next and when all
is performed in one database (the current database), I
reach the max size for Access (2GB). With the temp
backend, writing & deleting temp tables in that backend, I
have an option to compact, if necessary, but each time I
iterate through the loop I delete the database and
recreate it - thus the backend never grows to the 2GB masx.

v/r

Bob

 

[Douglas J. Steele]

Better might be to put the table in a temporary throw-away database. Then
you don't have to worry about the backend needing compacting because you're
creating and deleting tables in it.

Tony Toews has sample code at http://www.granite.ab.ca/access/temptables.htm

 

[Bob Bonta]

Thanx Raoul! As always, your recommendation worked. I
was not aware of the IN option on the SELECT statement.

Bob ...