Compact and Repair

[JString]

I think I might have made a fundamental mistake in the design of my front end
app. The general situation is that I created a dynamic SQl generator that
uses a series of temporary tables that are created and destroyed every time a
user builds his or her search parameters. Come to find out, it looks like
this process causes the front end to slowly accumulate in size with useless
ghosted data.

So, I don't know exactly what to do at this point but to keep compacting and
repairing, but I need a more permanent solution. Any suggestions would be
appreciated. Thanks in advance.

 

[a a r o n . k e m p f]

yah you just need to move to SQL Server

Access is one big bloat-- Now that SQL is free-- and it has been for a
decade-- it is not worth using JET any longer.

 

[Douglas J. Steele]

You could put your temporary tables into a temporary database.

Tony Toews has an example at http://www.granite.ab.ca/access/temptables.htm

 

[Guest]

Four possible solutions:

1) Rewrite all your queries.

2) Put all your temporary tables in a linked database.
Delete and refresh the temporary database as required.

3) Compact and repair as required.

4) But everything inside a DAO transaction. At end,
roll back the transaction. No changes are made to
the main database until you commit the transaction,
and that never happens.

I do a mixture of the first three. My temp SQL is all in VB.
I never create and destroy tables just to handle the temp
SQL. There are some single-record permanent tables
to handle dynamic values, and there are permanent tables
to store user-defined SQL, and permanent table to store
queries to get all the stuff you probably put in your temp
tables.

Then there are some temp tables for queries which are
too complex to do just in SQL. In Jet you can't do update
queries with aggregate functions. Some of these table need
to get compacted sometimes.

Then there are temp databases for the import/export
data. Code deletes the old temp database, copies the
template database, Create Table query creates the new
table in the new database using [database].

syntax.
Code links the new table to the main database if required
(or the old link is still there).

(david)

 

[So Sorry For Poor Aaron]

yah you just need to move to SQL Server

aaron is a ONE-TRICK PONY -- "move to SQL Server", and doesn't even know
squat about that trick.

Access is one big bloat-- Now that SQL is free--
and it has been for a decade-- it is not worth
using JET any longer.

Pea-brain, aaron, decade = ten years. Your dried-pea size/shape drain is
rattling around the inside of your head again. Only the wimpy, limited
Express version of Microsoft SQL Server is "free", that only for a couple of
years, and SQL Server Express has to be installed, and then "administered".

Message from the "Big" brothers: "Keep on pushing your limits. They want you
back in the cellblock." The are Big Bruce, Big Bubba, and Big Barney and
they approved this message.

 

[a a r o n . k e m p f]

SQL Server Express requires zero administration.
It's no more complex that Access-- SQL 2005 Express allows 'user
connections' so there is no such thing as 'registering a database'.

SQL Server just works. You don't need to admin it.
It is 100 times simpler than MS Access.

with SQL Server, things just work.

Access craps out twice a week for a dozen fulltime users? Then it's
just not worth it.

-Aaron

 

[a a r o n . k e m p f]

3 tier databases.. jesus you guys really need to grow up.

SQL Server has a real tempdb like this-- it takes Zero config
do MS Access solutions _REALLY_ need 3 tiers of Access crapware?

What a joke!

MUCH simpler to just keep your data and your queries-- where it
belongs-- on a database SERVER.
It's the same price as Access; in every regard.

But it's reliable.
Enterprise- Ready.

-Aaron

Four possible solutions:

1) Rewrite all your queries.

2) Put all your temporary tables in a linked database.
Delete and refresh the temporary database as required.

3) Compact and repair as required.

4) But everything inside a DAO transaction. At end,
roll back the transaction. No changes are made to
the main database until you commit the transaction,
and that never happens.

I do a mixture of the first three.    My temp SQL is all in VB.
I never create and destroy tables just to handle the temp
SQL.  There are some single-record permanent tables
to handle dynamic values, and there are permanent tables
to store user-defined SQL, and permanent table to store
queries to get all the stuff you probably put in your temp
tables.

Then there are some temp tables for queries which are
too complex to do just in SQL. In Jet you can't do update
queries with aggregate functions.  Some of these table need
to get compacted sometimes.

Then there are temp databases for the import/export
data. Code deletes the old temp database, copies the
template database, Create Table query creates the new
table in the new database using    [database].

syntax.
Code links the new table to the main database if required
(or the old link is still there).

(david)

I think I might have made a fundamental mistake in the design of my front end
app.  The general situation is that I created a dynamic SQl generatorthat
uses a series of temporary tables that are created and destroyed every time a
user builds his or her search parameters.  Come to find out, it lookslike
this process causes the front end to slowly accumulate in size with useless
ghosted data.

So, I don't know exactly what to do at this point but to keep compacting and
repairing, but I need a more permanent solution.  Any suggestions would be
appreciated.  Thanks in advance.

 

[Michael]

JString,

I see you are not getting much in the way of useful help. I will also not be
able to help much, but I can tell you that there may be nothing you can do if
you are using Access 2007.

There is a very serious issue in Access 2007 that causes bloating. Rewriting
your code, separating tables, using temps...will not work. I have a frontend
database (with no data) that goes from 11 Megs to 250 Megs in four hours and
hits more than a 1 Gig by the end of the day. Setting the database to compact
on close cannot help if there is more than one user.

The fundamental design of the database is not the problem. This database
application works fine in 2003. It is simply the problem of Access 2007.

Many people have seen this issue, but no one has been able to resolve it
properly. This is not the typical boating from normal usage. This is a
fundamental breakdown in the database system.

 

[JString]

Thanks for the help. I think I may have stumbled on a solution and I would
appreciate any feedback on this idea.

Currently the front end is set up to be shared by all users (per our IT
guy's request). Obviously, if the front end were distributed to each user's
machine, that would allow each copy to be compacted and repaired on close.
So here's what I think I'm going to do:

The front end will be stored on the network for use as a template only.
When the app is launched, it will create a copy of itself on the user's
machine if there is no pre-existing copy. If there is already a copy, it
will check and compare some 'last updated' value to see if it should be
replaced, and if these values don't match the copy will be destroyed and the
front end will re-copy itself onto the user's machine.

I think this should solve alot of problems at once.

 

[JString]

Thanks for the response. I'm not familiar with DAO transactions. Would you
mind going into a little more detail about that option?

Four possible solutions:

1) Rewrite all your queries.

2) Put all your temporary tables in a linked database.
Delete and refresh the temporary database as required.

3) Compact and repair as required.

4) But everything inside a DAO transaction. At end,
roll back the transaction. No changes are made to
the main database until you commit the transaction,
and that never happens.

I do a mixture of the first three. My temp SQL is all in VB.
I never create and destroy tables just to handle the temp
SQL. There are some single-record permanent tables
to handle dynamic values, and there are permanent tables
to store user-defined SQL, and permanent table to store
queries to get all the stuff you probably put in your temp
tables.

Then there are some temp tables for queries which are
too complex to do just in SQL. In Jet you can't do update
queries with aggregate functions. Some of these table need
to get compacted sometimes.

Then there are temp databases for the import/export
data. Code deletes the old temp database, copies the
template database, Create Table query creates the new
table in the new database using [database].

syntax.
Code links the new table to the main database if required
(or the old link is still there).

(david)

I think I might have made a fundamental mistake in the design of my front end
app. The general situation is that I created a dynamic SQl generator that
uses a series of temporary tables that are created and destroyed every time a
user builds his or her search parameters. Come to find out, it looks like
this process causes the front end to slowly accumulate in size with useless
ghosted data.

So, I don't know exactly what to do at this point but to keep compacting and
repairing, but I need a more permanent solution. Any suggestions would be
appreciated. Thanks in advance.

 

[John W. Vinson]

Thanks for the help. I think I may have stumbled on a solution and I would
appreciate any feedback on this idea.

Currently the front end is set up to be shared by all users (per our IT
guy's request). Obviously, if the front end were distributed to each user's
machine, that would allow each copy to be compacted and repaired on close.
So here's what I think I'm going to do:

The front end will be stored on the network for use as a template only.
When the app is launched, it will create a copy of itself on the user's
machine if there is no pre-existing copy. If there is already a copy, it
will check and compare some 'last updated' value to see if it should be
replaced, and if these values don't match the copy will be destroyed and the
front end will re-copy itself onto the user's machine.

I think this should solve alot of problems at once.

MUCH better approach. Your IT guy was simply *wrong* to have a single shared
frontend. That's a recipe for poor performance, rapid bloat and frequent
corruption.

 

[Guest]

:~) The method I don't use. For all I know it may have
stopped working in current versions of Access. The idea
is to use your temp tables to create your dynamic sql
in a VBA global variable, rollback the temp tables, then
execute the dynamic sql, or open the report or form or
whatever that uses the dynamic sql

A dao transaction looks like this:

Application.dbEngine.Workspaces(0).BeginTrans
Application.CurrentDB.execute "my create table query"
docmd.openform "my form using my table"

Application.dbEngine.Workspaces(0).Rollback


You don't have to use 'application' and 'workspaces(0)'
like that, but I put it in to show what I was doing. The
important point is that the same workspace is used for
the form as is used for create table query. Workspaces(0)
is (or used to be) the default workspace. dbEngine is the
copy of the dbengine used by Application. CurrentDB is
actually a method of Application. Another version of the
same thing would be:

dim ws as dao.workspace
dim db as dao.database
on error goto catch

set ws = Worspaces(0)
set db = currentDB
ws.begintrans
db.execute "my create table query"
docmd.openform "my build form"
ws.rollback

db.execute My_global_variable_sql

catch:
on error resume next
ws.rollback

(david)

Thanks for the response. I'm not familiar with DAO transactions. Would you
mind going into a little more detail about that option?

Four possible solutions:

1) Rewrite all your queries.

2) Put all your temporary tables in a linked database.
Delete and refresh the temporary database as required.

3) Compact and repair as required.

4) But everything inside a DAO transaction. At end,
roll back the transaction. No changes are made to
the main database until you commit the transaction,
and that never happens.

I do a mixture of the first three. My temp SQL is all in VB.
I never create and destroy tables just to handle the temp
SQL. There are some single-record permanent tables
to handle dynamic values, and there are permanent tables
to store user-defined SQL, and permanent table to store
queries to get all the stuff you probably put in your temp
tables.

Then there are some temp tables for queries which are
too complex to do just in SQL. In Jet you can't do update
queries with aggregate functions. Some of these table need
to get compacted sometimes.

Then there are temp databases for the import/export
data. Code deletes the old temp database, copies the
template database, Create Table query creates the new
table in the new database using [database].

syntax.
Code links the new table to the main database if required
(or the old link is still there).

(david)

I think I might have made a fundamental mistake in the design of my

front
end

app. The general situation is that I created a dynamic SQl generator that
uses a series of temporary tables that are created and destroyed every time a
user builds his or her search parameters. Come to find out, it looks like
this process causes the front end to slowly accumulate in size with useless
ghosted data.

So, I don't know exactly what to do at this point but to keep

compacting
and

repairing, but I need a more permanent solution. Any suggestions would be
appreciated. Thanks in advance.

 

[Tony Toews [MVP]]

There is a very serious issue in Access 2007 that causes bloating. Rewriting
your code, separating tables, using temps...will not work. I have a frontend
database (with no data) that goes from 11 Megs to 250 Megs in four hours and
hits more than a 1 Gig by the end of the day. Setting the database to compact
on close cannot help if there is more than one user.

Front end ... more than one user? Don't do that. Give each user
thier own copy of the FE.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[JString]

It would be nice if I could use vba only to handle the SQL, but unfortunately
I don't see any way around using those tables since the SQL is generated by a
continuous form which won't hold any data unless it has a recordsource.

Four possible solutions:

1) Rewrite all your queries.

2) Put all your temporary tables in a linked database.
Delete and refresh the temporary database as required.

3) Compact and repair as required.

4) But everything inside a DAO transaction. At end,
roll back the transaction. No changes are made to
the main database until you commit the transaction,
and that never happens.

I do a mixture of the first three. My temp SQL is all in VB.
I never create and destroy tables just to handle the temp
SQL. There are some single-record permanent tables
to handle dynamic values, and there are permanent tables
to store user-defined SQL, and permanent table to store
queries to get all the stuff you probably put in your temp
tables.

Then there are some temp tables for queries which are
too complex to do just in SQL. In Jet you can't do update
queries with aggregate functions. Some of these table need
to get compacted sometimes.

Then there are temp databases for the import/export
data. Code deletes the old temp database, copies the
template database, Create Table query creates the new
table in the new database using [database].

syntax.
Code links the new table to the main database if required
(or the old link is still there).

(david)

I think I might have made a fundamental mistake in the design of my front end
app. The general situation is that I created a dynamic SQl generator that
uses a series of temporary tables that are created and destroyed every time a
user builds his or her search parameters. Come to find out, it looks like
this process causes the front end to slowly accumulate in size with useless
ghosted data.

So, I don't know exactly what to do at this point but to keep compacting and
repairing, but I need a more permanent solution. Any suggestions would be
appreciated. Thanks in advance.