Compact database (1 Viewer)

[denbob]

We use a database (access) which is spread over 70 persons on their own computer. On this moment, each instance is set so that when closing access, the database gets compacted.? But his takes some time. As a result, some users don't log out.

Is there a way to do this (compacting) for example every 5 times?

Or is it possible that when the administrators work in the database (me and 2 collegues) that the database is compacted on our machines (but not on the co-workers). We work with a SQL-server which holds most of our tables and queries, except for a few local tables and queries. ( I think 80% on server, 20% local ( but small in size)).

So my questions :
1: can a compact database be programmed to be executed every week (also when someone works parttime)
2: can the administrators do the compacting and will this influence the other instances of the database (I don't think so, but it's worth asking)

( I all-ready have a code to set the compacting on and off. )

thx in advance (and apologies for the faults in my grammar, English is not my main language).

Bob

 

[Minty]

If a majority of your data is on SQL server I see little point in running the compact frequently at all.

How big is the FE database?

 

[isladogs]

There should be no need to compact the FE in a split database.
The only exception to that is where you are writing to / deleting from local FE tables on a regular basis. If so, why?

You should definitely switch off compact on close as it is known to be the cause of corruption.
There are several better solutions if you have database bloat issues:
1. run code to compact only when the FE exceeds a certain specified size
2. run a scheduled task to compact the database at specified intervals
3. Use a system whereby users download a fresh copy of the FE from the server each time they use the DB

 

[The_Doc_Man]

If the 20% of the DB tables that are local actually get updated now and then, you can do the C&R on your local systems, but the BE probably shouldn't EVER be treated to ANY automatic cleanup operations. You should only do such operations during a scheduled down-time. For a 70-person shop, odds are very high that you will have issues, so schedule a time - perhaps once a week? - during which you can do required maintenance ops.

Colin (isladogs) offers the idea to test file size as the criterion for deciding whether the FE needs a C&R. If you have any doubts about how to do that, look at the FileSystemObject as a way to see the size of the FE file. The answer will be in bytes and, due to Access limitations, can NEVER be bigger than 2 Gb.

Here's another thought, though. If your local tables are merely used for temporary storage but never anything permanent, Colin's #3 idea is better.

 

[Pat Hartman]

Agree with removing the C&R

But, C&R on local machine should be fast. Users should have FE on their LOCAL machines. It should NOT be on the server and it should NEVER, EVER be shared. To summarize, each person needs his own personal copy of the FE stored on his C: drive.

C&R over a network connection can be painfully slow. C&R of a shared FE is a recipe for disaster.

 

[denbob]

Thank you for your advise. We will be looking into moving everything to the SQL server so that a local table will be the exception. And the compacting will be limited to once a week.
The update of the FE every time: we'll be taking that in consideration.
To resume :
the C&R will be limited (once a week) .
the tables will be moved to the backend, local table will be an exception
each person allready has his/hers own copy on the local machine, we'll be taking a look on the automatic update

I will also take a look in executing our queries by placing them in the BE (all of them). Now they are sometimes in the FE and sometimes in the BE, which is very confusing for me. But it seems to me that when we experience a slow working of the FE, it's because a lot of data has to be moved over the network. And when combined with VPN, the speed is very slow. That's an other issue, but I'll take it one step at a time.

Thanks for all your advise !