Re: Make-Table Qry in backend database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I agree,

and if you have a lot of temp tables then it's best to create a temp.mdb
which you can copy in a temporary folder.
When this is done you check whether the attachments are still valid if not
you need to refresh them.
Creating the tempfile might take a long time therefor the above procedure.

- Raoul

"Douglas J. Steele" wrote:

> 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
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Bob Bonta" <bob.bonta@xxxxxxxxxxx> wrote in message
> news:110f01c54024$c8de9d80$a501280a@xxxxxxxxxx
> > 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
> >>-----Original Message-----
> >>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" wrote:
> >>
> >>> 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
> >>>
> >>.
> >>
>
>
>
.

Relevant Pages

  • Re: Temp tables vs Permanent table with deletes
    ... Table expressions are literally inline macros, the SQL is expanded into ... using a persistent table for my temp storage - something surprising to ... many programmers who are used to CPU bound tasks that _always_ run ... an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... CTE the optimiser hasn't a real idea in most cases (given real world SQL) how many rows will be returned on the intermediary steps, so you get a general plan. ... The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here;) ... Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Temp tables vs Permanent table with deletes
    ... If you are interested rather than taking our word for it Google around for tempdb blocking and research the problem. ... Table expressions are literally inline macros, the SQL is expanded into the main query - there is no encapsulation benefits in using table expressions because of this expansion. ... The worst 'improvements' come from doing things like avoiding the table creation overhead by using a persistent table for my temp storage - something surprising to many programmers who are used to CPU bound tasks that _always_ run faster if I do less work here;) ... Using a permanet table to act as a temporary table just causes blocking, fragmentation and additional logging because people usually locate it in an application database rather than tempdb. ...
    (microsoft.public.sqlserver.programming)
  • Re: Tips on domain aggregate replacements
    ... -Create a 2nd backend DB to house the temp tables. ... This 2BE would only contain temp tables nothing else and sit on the users PC. ... management program, so it is always the next twelve months from the day the report is run (as of today, this would be November 05 through October 06. ... Month1Total -> DSum (blah, blah, blah where date is within next month from today ...
    (microsoft.public.access.modulesdaovba)
  • Temp Database problems with Access 2007
    ... I've been using Tony Toew's Temp table module and I've now upgraded to ... The database is still in 2003 format. ... ' This subroutine illustrates how to use a temporary MDB in your app. ...
    (comp.databases.ms-access)