MDE file and temp tables for reports

[MES via AccessMonster.com]

I have a question about splitting the database and making into an
MDE file to distribute front ends. I have several reports in my database
that run and use a temporary table that is populated by a query. When I open
a report, I select certain parameters, and then a temp table is appended to
include pertinent information according to the parameters and the report
fields. The report fields are populated from the temp table. When the
report closes, the records in the temp table are deleted.

If I split this database and distribute copies of the front end to users,
will I have issues with these temp tables? For example, if one user running
a report and has it open, and the temp table is populated. If another user
in another location opens the same report and populates the temp table, what
(if any) trouble will I run into there?

Any help here would be greatly appreciated.

 

[Jerry Whittle]

There are ways to avoid this problem.

1. Avoid the usage of temp tables. Create the report from a query that does
everything needed by the temp table. This is ultimately the best solution.

2. Give each user a copy of the FE and have the temp tables in it. This is
an inefficient, but practical solution.

3. If for some reason you can not get around temp tables and also not able
to give each user a copy of the FE, then dynamically create the temp tables
with unique names; run reports off of these tables with different names; then
remember to drop these tables. Yikes!

 

[MES via AccessMonster.com]

I think that # 2 will be my only option since at this point I have no way of
getting around temp tables. I can give each user a copy of the FE. This way,
when a user runs a report, the data is stored on their local temp table,
right? No one else can access.

This is a relatively small application that I have created, so I'm not too
worried about inefficiency.

Thanks for your response.

There are ways to avoid this problem.

1. Avoid the usage of temp tables. Create the report from a query that does
everything needed by the temp table. This is ultimately the best solution.

2. Give each user a copy of the FE and have the temp tables in it. This is
an inefficient, but practical solution.

3. If for some reason you can not get around temp tables and also not able
to give each user a copy of the FE, then dynamically create the temp tables
with unique names; run reports off of these tables with different names; then
remember to drop these tables. Yikes!

I have a question about splitting the database and making into an
MDE file to distribute front ends. I have several reports in my database

[quoted text clipped - 11 lines]

Any help here would be greatly appreciated.

 

[David W. Fenton]

I think that # 2 will be my only option since at this point I have
no way of getting around temp tables. I can give each user a copy
of the FE. This way, when a user runs a report, the data is
stored on their local temp table, right? No one else can access.

Put the temp tables in a separate MDB file, instead of in the front
end. If you don't, your front end will bloat terribly and become a
performance bottleneck.

 

[MES via AccessMonster.com]

Is there any way to lock the temp tables while someone else has them open?

There are ways to avoid this problem.

1. Avoid the usage of temp tables. Create the report from a query that does
everything needed by the temp table. This is ultimately the best solution.

2. Give each user a copy of the FE and have the temp tables in it. This is
an inefficient, but practical solution.

3. If for some reason you can not get around temp tables and also not able
to give each user a copy of the FE, then dynamically create the temp tables
with unique names; run reports off of these tables with different names; then
remember to drop these tables. Yikes!

I have a question about splitting the database and making into an
MDE file to distribute front ends. I have several reports in my database

[quoted text clipped - 11 lines]

Any help here would be greatly appreciated.

 

[MES via AccessMonster.com]

What do you mean bloat? Also, please explain to me how to use a separate MDB
file for the temp tables - is that efficient?

 

[David W. Fenton]

What do you mean bloat?

If you are using temp tables, you'll be adding and deleting records.
As the data is deleted, the space taken up by those deleted records
is not recovered until you compact the database. This can lead to
extensive fragmentation in your front-end MDB, and cause terrible
performance problems. So, you'd then need to compact regularly.

This is why a front end is an unsuitable location to store temp
tables.

Also, please explain to me how to use a separate MDB
file for the temp tables - is that efficient?

You just create a tmp.mdb with your temp tables that you store in
the same folder as you front end MDB, and replace the temp tables in
the front end with links to the tmp.mdb tables.

 

[MES via AccessMonster.com]

What about if the file is an MDE file? Wouldn't that compact automatically?

[quoted text clipped - 7 lines]

What do you mean bloat?

If you are using temp tables, you'll be adding and deleting records.
As the data is deleted, the space taken up by those deleted records
is not recovered until you compact the database. This can lead to
extensive fragmentation in your front-end MDB, and cause terrible
performance problems. So, you'd then need to compact regularly.

This is why a front end is an unsuitable location to store temp
tables.

Also, please explain to me how to use a separate MDB
file for the temp tables - is that efficient?

You just create a tmp.mdb with your temp tables that you store in
the same folder as you front end MDB, and replace the temp tables in
the front end with links to the tmp.mdb tables.

 

[David W. Fenton]

What about if the file is an MDE file? Wouldn't that compact
automatically?

Uh, what? Why would an MDE file compact automatically?

Sure, you can set COMPACT ON CLOSE to YES, but that's very unwise,
as you don't get a choice to cancel it, and a compact can sometimes
corrupt a database beyond repair.

Perhaps you're confusing the bloat the comes from recompiled code
with the bloat that comes from adding and deleting records from
tables. Code bloat doesn't exist in an MDE because it's precompiled
and can't become uncompiled. But you can certainly add/delete
records in an MDE and that will cause bloat and you will need to
compact to get rid of the bloat.

There shouldn't be any temp tables in any front end, whether it's
and MDB or an MDE. Temp tables should be in a separate temp database
that either is never compacted, or is replaced by a clean, empty
template database when you want to clear out the crud.