What do you mean by "shared" in this case? Shared across front-ends? Across users? Across back-ends? I'm not sure I see the issue you're referring to so please educate me pls
Automate Compact/Repair
- Thread starter RogerCooper
- Start date
- Local time
- Today, 17:32
- Joined
- Feb 19, 2002
- Messages
- 47,572
I mean that if multiple users share the same BE, which is typical, one of the FE users should NOT be trying to compact the BE. And since only the BE should ever need compacting, I would need more information to be more specific.
In a properly designed shared application, each user has his own personal copy of the FE and the FE should not bloat because you should not be writing code that causes bloat. The BE grows as records are added and so it needs compacting weekly or monthly depending on how rapidly the database grows. If you are inserting/deleting rows continuously or doing make-tables, there are better methods that don't bloat but we need to know what is causing the bloat and whether it is the FE or BE.
In a properly designed shared application, each user has his own personal copy of the FE and the FE should not bloat because you should not be writing code that causes bloat. The BE grows as records are added and so it needs compacting weekly or monthly depending on how rapidly the database grows. If you are inserting/deleting rows continuously or doing make-tables, there are better methods that don't bloat but we need to know what is causing the bloat and whether it is the FE or BE.
Ha yes, I understand. I had assumed each user has a local FE that links to server BE i.e., no one is sharing FE (I should stipulate this architecture assumption). Agreed that there's generally no reason to C&R FE.
FE users DO NOT use such an admin tool: thus the name "Admin Compact DB tool". I suppose I could setup up a password to make sure end users never stumble onto it and try to use.
It may indeed be worth it to buy such a tool as you mention. I always try to see if some simple tool might do an adequate job first; we'll see how it goes with this client.
Thanks so much for sharing your knowledge
FE users DO NOT use such an admin tool: thus the name "Admin Compact DB tool". I suppose I could setup up a password to make sure end users never stumble onto it and try to use.
It may indeed be worth it to buy such a tool as you mention. I always try to see if some simple tool might do an adequate job first; we'll see how it goes with this client.
Thanks so much for sharing your knowledge
Lightwave
Ad astra
- Local time
- Today, 22:32
- Joined
- Sep 27, 2004
- Messages
- 1,537
I'm late to the party and plenty of good suggestions above but here is something that I have used in the past it is a combination of a vb script and a ms access function
rounduptheusualsuspects.org
rounduptheusualsuspects.org - rounduptheusualsuspects Resources and Information.
rounduptheusualsuspects.org is your first and best source for all of the information you’re looking for. From general topics to more of what you would expect to find here, rounduptheusualsuspects.org has it all. We hope you find what you are searching for!
rounduptheusualsuspects.org
Etxezarreta
Member
- Local time
- Today, 23:32
- Joined
- Apr 13, 2020
- Messages
- 175
Hi,
We are several people working on our own FE, and I will migrate the BE into SqlAzure soon.
I guess SqlAzure takes in charge the compacting of the BE, but I am not sure about this.
Thanks i nadvance.
Etxe.
We are several people working on our own FE, and I will migrate the BE into SqlAzure soon.
I guess SqlAzure takes in charge the compacting of the BE, but I am not sure about this.
Thanks i nadvance.
Etxe.
- Local time
- Today, 14:32
- Joined
- Oct 29, 2018
- Messages
- 22,630
Hi. I could be wrong, but I think C&R is only an Access concept because it's a "file system" database. I don't think it applies to SQL Azure. Cheers!
Etxezarreta
Member
- Local time
- Today, 23:32
- Joined
- Apr 13, 2020
- Messages
- 175
So this means that we don't have to worry about this matter doesnt it?
- Local time
- Today, 14:32
- Joined
- Oct 29, 2018
- Messages
- 22,630
No, I don't think so. But, if you're using an Access FE, then you may still have to worry about it for the FE.
Etxezarreta
Member
- Local time
- Today, 23:32
- Joined
- Apr 13, 2020
- Messages
- 175
Ok.
Yes, the FE bloating is one of my main concerns.
Thank you theDBguy.
Etxe.
Yes, the FE bloating is one of my main concerns.
Thank you theDBguy.
Etxe.
- Local time
- Today, 14:32
- Joined
- Oct 29, 2018
- Messages
- 22,630
One way to handle that is to simply download a fresh copy of the master FE every time.
Etxezarreta
Member
- Local time
- Today, 23:32
- Joined
- Apr 13, 2020
- Messages
- 175
We don't have a wan or a lan, we will have only local copies of the FE in every computer.
The option compact and repair is activated.
Should me put every day a copy of the FE in a shared file and download it?
The option compact and repair is activated.
Should me put every day a copy of the FE in a shared file and download it?
- Local time
- Today, 14:32
- Joined
- Oct 29, 2018
- Messages
- 22,630
If you don't have a LAN, then you can put a master copy of the FE on the local drive and simply copy it each time the user runs the app.
- Local time
- Today, 17:32
- Joined
- Feb 19, 2002
- Messages
- 47,572
RDBMS' such as Azure are more efficient about managing data storage than Access but they still require maintenance. That would never be the responsibility of users though. It would always be the responsibility of the DBA (Database Administrator)
If you are not all on a LAN, how are you distributing new copies of the FE.
If you are not all on a LAN, how are you distributing new copies of the FE.
isladogs
MVP / VIP
- Local time
- Today, 22:32
- Joined
- Jan 14, 2017
- Messages
- 19,003
I've no idea regarding SQL Azure as I've never used it, but SQL Server has feature called Shrink which works in a very similar way to compacting in Access
Etxezarreta
Member
- Local time
- Today, 23:32
- Joined
- Apr 13, 2020
- Messages
- 175
ok, thanks. We will distribute FE copies using sharepoint.
Etxe.
- Local time
- Today, 16:32
- Joined
- Feb 28, 2001
- Messages
- 30,487
Almost all active SQL back ends have options to rearrange data segments based on things like "anticipated bucket fill" or whatever else they call it. The idea is that many SQL back ends allow you to "suggest" that each data block or bucket is targeted for only 60% fill rate. So even if you only have 300 MB of data it will occupy 500 MB of space because the buckets don't fill up. That way, if you need to insert a record in a particular block to keep the sort order correct, you have room until you reach whatever threshold triggers a "bucket split" operation, essentially a rewrite of certain segments in the database file to redistribute and re-order data so as to make the DB "optimized" again. ORACLE was pretty big on tools to automate the bucket splits. ShareBase had the same concept. Since the ShareBase engineers left that company to form SYBASE, odds are that the automatic reformatting is part of their package too.
That is a rough description of the operation most closely resembling the equivalent to the Access compact and repair. Of course, every vendor will have a different name for that feature. Heavens forfend that they should all do something that other folks could simply understand without having to first take one of their proprietary DB management classes...
That is a rough description of the operation most closely resembling the equivalent to the Access compact and repair. Of course, every vendor will have a different name for that feature. Heavens forfend that they should all do something that other folks could simply understand without having to first take one of their proprietary DB management classes...
Similar threads
