make table not replacing table on back end

[deb]

Access 2003

I have split my database and distributed the FE to users
The database has an ODBC connection to Lotus Notes.
I create a make table query called q00NonconformMKTbl to dump the data from
the Lotus Notes ODBC connection to the tNonconformance table.

But since the database is split when i run the Make table it is only
updating on my instance of the FE.

Is there a why to do a Make table and specify to delete and recreate the
table in the BE?

It was making me crazy until i figured why only my db was updating with
lates data.

 

[Jerry Whittle]

Instead of dropping a table and creating a new one, create a delete query to
remove all the records from the table in the BE then do an append query to
populate it with the new records.

 

[deb]

Thank you for your response.
My main issue was that I needed to delete and append to a table on my back
end.
Currently it is deleting and creating/appending a table on my FE.

I found a way to make it run the query on the BE table.

Thank you for your help!!

 

[John W. Vinson]

My main issue was that I needed to delete and append to a table on my back
end.

You're mistaken, Deb. You do NOT need to delete the *table* and create a new
table to accomplish this task.

You need to delete the *data in the table* - with a Delete query, which can be
run from the frontend - and then run an Append query to refill the table.

This is actually much more efficient; there's a whole lot of overhead with
system tables, indexes, etc. that go into creating a new table. This can all
be avoided by keeping a table (which will usually be empty), filling it as
needed, and emptying it again.

Whichever way you do it, you should regularly Compact the backend; whether you
delete a table or just the data in the table, Access won't recover the disk
space until you do so.

 

[David W. Fenton]

Whichever way you do it, you should regularly Compact the backend;
whether you delete a table or just the data in the table, Access
won't recover the disk space until you do so.

I think making regular batch deletes a part of your app's operation
is a mistake. Records that need to be deleted and replaced with
something else are temporary data and belong in a temp database, not
in the back end.

I recently helped a company troubleshoot problems with an app they
have that uses Jet Replication. They were hitting the point where
synching their replicas would fail, and it turned out the
MSysTombstone table (the table that tracks record deletions; think
about what replication is doing and you'll realize there has to be a
table tracking deletions) was enormous. When I deleted all the data
from one of their replicas, half the data was the tombstone table
(350MBs for tombstones only, vs. 730MB for the original replica).
Anyway, the issue was caused by a design that deleted huge batches
of records and replaced them wholesale with new records on a regular
basis.

Now, absent replication this would not have completely broken the
app, but it was a bad idea even in a non-replicated environment. The
basic principle is to avoid churning the data in your back end. This
minimizes the fragmentation of the data file, which in turn enhances
performance, but also means you don't have to compact the back end
as often.

Storing temp records in your back end is simply a bad idea.

 

[John W. Vinson]

I think making regular batch deletes a part of your app's operation
is a mistake. Records that need to be deleted and replaced with
something else are temporary data and belong in a temp database, not
in the back end.

I recently helped a company troubleshoot problems with an app they
have that uses Jet Replication. They were hitting the point where
synching their replicas would fail, and it turned out the
MSysTombstone table (the table that tracks record deletions; think
about what replication is doing and you'll realize there has to be a
table tracking deletions) was enormous. When I deleted all the data
from one of their replicas, half the data was the tombstone table
(350MBs for tombstones only, vs. 730MB for the original replica).
Anyway, the issue was caused by a design that deleted huge batches
of records and replaced them wholesale with new records on a regular
basis.

Now, absent replication this would not have completely broken the
app, but it was a bad idea even in a non-replicated environment. The
basic principle is to avoid churning the data in your back end. This
minimizes the fragmentation of the data file, which in turn enhances
performance, but also means you don't have to compact the back end
as often.

Storing temp records in your back end is simply a bad idea.

Agreed... but storing temp *tables* in your front OR backend is an even worse
idea.

Thanks for the suggestion about using a scratch database. The optimum solution
is not to store temporary records at all, but to use dynamic queries, of
course!

 

[David W. Fenton]

Thanks for the suggestion about using a scratch database. The
optimum solution is not to store temporary records at all, but to
use dynamic queries, of course!

Temp records are really helpful in some cases, and don't take nearly
as long to create as you might think. I used to redo Access 2
reports to use temp tables when the recordset got "too complex" (a
problem that hardly ever happens since the introduction of Jet 3.x
and later). Replacing the dynamicy queries with temp tables
populated on the fly did not slow down the reports at all -- in some
cases it speeded things up dramatically.

I use temp tables for search results forms in nearly all my apps. It
makes it much easier to have a flag field for selecting/eliminating
records in a resultset that you want to do things with.

Almost all of my apps have a minimum of two back ends, the shared
back end data tables and a temp database stored in the same folder
as the front end. Some apps have temp databases for shared
"temporary" results (my two latest both do, in fact) that are stored
in the same folder as the back end.

 

[John W. Vinson]

Temp records are really helpful in some cases, and don't take nearly
as long to create as you might think. I used to redo Access 2
reports to use temp tables when the recordset got "too complex" (a
problem that hardly ever happens since the introduction of Jet 3.x
and later). Replacing the dynamicy queries with temp tables
populated on the fly did not slow down the reports at all -- in some
cases it speeded things up dramatically.

I use temp tables for search results forms in nearly all my apps. It
makes it much easier to have a flag field for selecting/eliminating
records in a resultset that you want to do things with.

Almost all of my apps have a minimum of two back ends, the shared
back end data tables and a temp database stored in the same folder
as the front end. Some apps have temp databases for shared
"temporary" results (my two latest both do, in fact) that are stored
in the same folder as the back end.

Interesting. Do you use code to create a new .mdb "scratch" database using
CreateDatabase, or do you have a dedicated database for the purpose? If the
latter, might this be one of the (probably rare) cases where Compact on Close
would be appropriate?

 

[Armen Stein]

Interesting. Do you use code to create a new .mdb "scratch" database using
CreateDatabase, or do you have a dedicated database for the purpose?

In our apps, we use a persistent dedicated "work" database to hold
temporary and working tables. It lives in the same folder as the
front-end application and is delivered at the same time. Our J Street
Access Relinker (http://ow.ly/M56Q) relinks to it silently and
automatically.

Our work tables are for storing data for this local session only.
They're used for complex reports, Word merges, Excel exports, complex
updates and processing, etc. The objects themselves are persistent
and the structure is usually static except when we add columns for
variable Excel exports. We typically Delete * at the beginning of our
process, and leave the tables loaded at the end to facilitate
diagnostics.

If the
latter, might this be one of the (probably rare) cases where Compact on Close
would be appropriate?

If we're concerned about database bloat in the work database, we
compact it using VBA during the startup of the application. We
haven't used Compact on Close, because we like to be able to trap any
errors that might occur.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

On 8 Feb 2010 02:27:55 GMT, "David W. Fenton"


Interesting. Do you use code to create a new .mdb "scratch"
database using CreateDatabase, or do you have a dedicated database
for the purpose? If the latter, might this be one of the (probably
rare) cases where Compact on Close would be appropriate?

Years ago, I kept a template database, tmp.bak, and copied it over
top of the tmp.mdb each time the app was loaded, but I've gotten
lazy about that and do nothing at all. I just let it bloat. I really
don't give a rat's ass that the temp database is bloated.

I wouldn't use compact on close for that because:

1. nobody ever opens the tmp.mdb, so it would never get called, so
what's the point?

2. should a user accidentally open it and it got corrupted during
and uncancellable compact, I'd have a support call.

No benefit, potential downside -- the obvious choice is to not use
it.

I'm pretty categorical about this. There is NO circumstance in which
anyone should ever use COMPACT ON CLOSE.

 

[John W. Vinson]

Years ago, I kept a template database, tmp.bak, and copied it over
top of the tmp.mdb each time the app was loaded, but I've gotten
lazy about that and do nothing at all. I just let it bloat. I really
don't give a rat's ass that the temp database is bloated.

Probably safe in most cases - but if the user *often* creates *big* temp
tables, couldn't it get to the 2GByte limit and generate another unwelcome
support call?

I'm pretty categorical about this. There is NO circumstance in which
anyone should ever use COMPACT ON CLOSE.

<g> Sounds good to me!

 

[David W. Fenton]

Probably safe in most cases - but if the user *often* creates
*big* temp tables, couldn't it get to the 2GByte limit and
generate another unwelcome support call?

Given that the source databases are not even close to having 2GBs of
data (I don't even have any that are close to 500MBs), I can't see
how the temp db could get that large, even if it had, say, 500%
bloat overhead. Jet really does re-use data pages instead of just
allocating new ones, so bloat stops at a certain point, even with
daily churn.