MS Access Multiuser - Temp Tables

Author

Temp Tables

Gwhit

2005-09-20, 1:25 pm

Hello,

I'm working with a perfectly running 16-user MS Access 2002 database on XP
Pro, and have been adding enhancements and interactivity with Outlook and
Word since April. It is a single front-end on an NT server with data tables
split into its own folder and linked to the front end.

My question is, for users who need to pull in data to temporary tables for
report-building, should these temp tables be linked as well? There is a lot
of auto-deleting occurring of these tables as they open up their interfaces
to build their reports.

Bottom-line question, should these temp tables be local to the front-end or
be linked?

Thanks,

Rick B

2005-09-20, 1:25 pm

Why create a temp table? Can't you use a query to pull the appropriate
records and then build the report on that query?

--
Rick B

"Gwhit" <Gwhit@discussions.microsoft.com> wrote in message
news:74C0597B-6244-4FFC-A130- 63AC9A32BE81@microso
ft.com...
> Hello,
>
> I'm working with a perfectly running 16-user MS Access 2002 database on XP
> Pro, and have been adding enhancements and interactivity with Outlook and
> Word since April. It is a single front-end on an NT server with data
tables
> split into its own folder and linked to the front end.
>
> My question is, for users who need to pull in data to temporary tables for
> report-building, should these temp tables be linked as well? There is a
lot
> of auto-deleting occurring of these tables as they open up their
interfaces
> to build their reports.
>
> Bottom-line question, should these temp tables be local to the front-end
or
> be linked?
>
> Thanks,

Gwhit

2005-09-20, 1:25 pm

Thanks for responding.

Most of the reports on here are query-based and work beautifully. However,
overall speed is an issue with the particular reports I'm referring to in
this post. I've developed a tool within the database for the supervision
here, (some of whom have little PC experience) where they can "piece
together" all manner of data (much of it dating back for years) through
easy-to-use forms. These particular reports simply bog down and it is
clearly much faster to "grab and go" using temporary tables. I'm just
wanting to determine the best location for these tables, if there in fact is
any difference, given their purpose within the database.

"Rick B" wrote:

> Why create a temp table? Can't you use a query to pull the appropriate
> records and then build the report on that query?
>
> --
> Rick B
>
>
>
> "Gwhit" <Gwhit@discussions.microsoft.com> wrote in message
> news:74C0597B-6244-4FFC-A130- 63AC9A32BE81@microso
ft.com...
> tables
> lot
> interfaces
> or
>
>
>

John A.

2005-09-20, 8:24 pm

Put the temp tables in the front ends, and provide some mechanism for
periodically compacting the front ends. I agree with both you - ideally we
don't use temp tables, but sometimes it's just much simpler and faster,

John

david epsom dot com dot au

2005-09-23, 3:24 am

Our temp tables are in the FE database, and we just delete
records and requery.

If you delete tables, you will eventually run into the
maximum number of created objects you can create (or at
least that is how it used to work). Both deleting tables
and deleting records lead to database growth. You can compact,
or you can use linked tables: with linked tables you can
delete the temp database and create a new temp database.

A local linked database is faster than a networked linked
database, but is still not as fast as using local tables.
Also, if you have automatic re-link code, you have to
differentiate between the link files, and between the link
folders.

Another method was to create a transaction, and roll back
the transaction after running the report - the changes were
never applied to the database file, only the transaction
temp file.

(david)

"Gwhit" <Gwhit@discussions.microsoft.com> wrote in message
news:BE4788C8-A2CD-4186-AE0E- 09490BD7EBBC@microso
ft.com...[color=darkred]
> Thanks for responding.
>
> Most of the reports on here are query-based and work beautifully.
> However,
> overall speed is an issue with the particular reports I'm referring to in
> this post. I've developed a tool within the database for the supervision
> here, (some of whom have little PC experience) where they can "piece
> together" all manner of data (much of it dating back for years) through
> easy-to-use forms. These particular reports simply bog down and it is
> clearly much faster to "grab and go" using temporary tables. I'm just
> wanting to determine the best location for these tables, if there in fact
> is
> any difference, given their purpose within the database.
>
>
> "Rick B" wrote:
>

Sponsored Links