Database Size, Table Back End, Compact and Repair (1 Viewer)

[Tim Bedborough]

Hi everyone.

Sorry for the ramble, hope it makes sense.

I'm using Access for data source back end tables as well as front end windows (objects - forms, reports etc). Back end and front end are split due to multi user front end requirements. All on on a local area network.

We had a minor scare today. Some of my form combo boxes were unable to show the options in the list. Not all.

I did a 'compact and repair' on the back end which fixed things. The old back end went from 116,000 KB to 20,000 KB. (I haven't done a C&R for a while).

I did a C&R on the front end but this dropped from 11300 KB to 11200 KB so didn't perceive this would have made much difference. The back end C&R was done first and fixed the issue in the front end on the larger file size before I did a C&R on the front end.

We are running 15 users approx although not all use the database or network at the same time so is probably limited to 6 or so at any 1 time. I have had feedback that this might be reaching the back end capacity of what Access can achieve in a multi user environment but we have run it this way on Access 2002 right up to 2015 when we moved over to Access 15 (or whatever it was in 2015) and then 365 and had a back end and front end rebuild. Moved from .mdb to .accdb.

So after all that the question is this. Does anyone consider these file sizes an issue with Access or the number of users? Reducing the back end by 1/5th approx fixed things but no idea if this was a file size issue or something else that the repair feature in Access identified and fixed.

We don't really want to have to move away from the current Access database set up but if file sizes will cause object functionality issues (e.g. combo drop lists), i'd rather be thinking about options before it breaks rather than when we get to capacity.

Any thoughts or tips much appreciated.

Thanks.

 

[vba_php]

tim,

it's been a while for me since I've dealt with capacity issues, but I did a search for you on the terms that will most likely yield good material. maybe you can get something from it:

https://www.google.com/search?q=ms+access+concurrent+users+limit

 

[The_Doc_Man]

The practical limits for Access are such that 5 or 6 users online at once isn't usually the issue. I've had more than that on a bigger database.

The issue is not how many users are doing something but more specifically what they are doing and may also relate to what kind of network they are riding. You say everyone is local thus probably on a LAN as an intranet for your site. That is good and not likely to be easy to change anyway. So... let's talk about DB speed killers.

First, is everyone touching the same table when they do what they do? If so, then be sure that you have placed indexes on the fields most likely to be queried. You can have up to 10 indexes (counting whatever is your prime key in that limit). I'm not saying you need to put 10 indexes. But if you most often search or select on specific fields, those fields should be indexed.

Second, check that you have optimistic locking on all updates, inserts, and deletes. For queries that ONLY are used to generate reports, set NO LOCKS. Forms bound to an underlying recordset should be set for optimistic locking. If you have unbound forms that synthesize some result and apply it via SQL, see earlier comment on updates and inserts and deletes.

I'm going to ruminate for a moment. The approximately 6-to-1 reduction of the BE by a C&R just says you have a bunch of updating going on. Can't say that is much of an issue. However, the minuscule reduction of the FE says something about your connections to the BE.

Normally, your queries generate temporary lists of records selected by that query. This leads to bloat of the FE because that is where Access is actually working. Which suggests that you don't have a lot of query activity originating in the FE. I just think it is a tiny bit odd. By any chance do your activities always involve direct table access?

If that is true, you are perhaps under-utilizing the power of queries. But this is NOT a certainty on my part. I.e. as I said, I'm ruminating. And if it all works, then I can't say you did anything wrong.

But if you are running into speed issues, queries can sometimes help make things go a little faster if you put the filters and ordering and other stuff in the query that is then called by a form, rather than putting that filtration entirely in the .Recordsource of the form or report. The question is WHERE is the recordset manipulation being done? It is more efficient if JET or ACE does at least SOME of it than if the GUI that drives forms has to do most of it. And the effect is usually subtle in any case.

 

[Micron]

I agree that the size and number of concurrent users is likely not the issue. Let's not forget that he second part of C&R is repair, which is probably more relevant to the fix than shrinking of file size. One thing that can cause issues it how people terminate their connection. Hopefully, no one is "pulling the plug" so to speak, otherwise corruption is highly likely to recur. You should consider developing a process to back up the be on a regular and frequent basis, and perform C&R on it occasionally.

 

[Tim Bedborough]

Thanks for feedback. End of day and brain hurts so one for the morning. I'll update thread if anything to report once I've had a closer look.

 

[The_Doc_Man]

Absolutely concur with Micron re regular backup and regular (but less frequent) C&R

 

[CJ_London]

So after all that the question is this. Does anyone consider these file sizes an issue with Access or the number of users? Reducing the back end by 1/5th approx fixed things but no idea if this was a file size issue or something else that the repair feature in Access identified and fixed.

Your db is very small so I don't think file size is the issue

I'm using Access for data source back end tables as well as front end windows (objects - forms, reports etc). Back end and front end are split due to multi user front end requirements. All on on a local area network.
...
...
I did a C&R on the front end

your explanation of your setup does not go far enough. Front ends should be local to each user, not on the network as implied by what you have said. If users are actually sharing the front end, this is most likely where your problem lies. The fact it has not been an issue until now just means you've been lucky.

 

[The_Doc_Man]

Oh, I missed that implication. Interesting catch, CJ.

Saying you do a C&R on "the front end" implies one of two things. Either (a) everyone runs the FE from the shared folder which is why there is THE front end or (b) everybody uses the auto-downloader batch job that has been presented in other articles in this forum, and thus there really IS a single FE that counts. In the latter case, a C&R is not necessary since the downloader we mentioned replaces any prior copy of the FE with a fresh one each time.

Just so you will understand CJ's point - and it is absolutely a good one - if everyone points Access itself (via browsing) or a launcher icon to the shared copy, then everyone uses that copy. That means that the file locks are all on the shared copy on the host machine. Windows via SMB protocols is capable of locking parts of a file, but if you have a commonly used part (like a particular popular form) then that part gets used a lot and gets locked a lot.

In that case, the system interactions require the host computer to arbitrate locking among all users of that file, and the number of potential interferences increases factorially. Further, that arbitration is over the network, so gets slower by a lot as the number of participants goes up. Distributed lock management makes sharing possible but has a cost in terms of overhead. It is possible that your slowdown is due to this factor.

IF the FE files are separated so that each user has their own copy, then the only public locks are on the BE file and with optimistic locking, there is only a minimal chance that opening a commonly used form will run into lock collisions. IF you have a single FE file that is shared, stop that IMMEDIATELY. Even though no one should be able to open a form for modification, it is still possible for lock collisions to cause a given user to get badly blocked which could lead to corruption. For a read-only FE file, this corruption would be rare - but no guarantees of how close to zero it would be.

 

[Tim Bedborough]

Hi all.

I'm happy we have a unique front end for each user. Not a single front end that everyone uses.

I haven't checked out DocMan's suggestions yet - things to do.

My C&R on the back end fixed the front end issue.

Funny enough (not so funny really), I came into work this morning and the same problem happened again.

I did a C&R on front end first which made no difference. I did a C&R on back end and again it fixed it. Will be interesting to see for how long though.

I'm wondering if its just a painful coincidence that 2 people were in the same place at the same time although can't easily establish this. Seems a bit too easy an answer.

 

[CJ_London]

I'm happy we have a unique front end for each user. Not a single front end that everyone uses.

I did a C&R on front end first which made no difference.

I presume you mean you did a C&R on every users front end?

 

[Tim Bedborough]

We have a master front end. I modify a 'beta' version that I replace the master with after all mods completed, C&R etc. When network users log in a script gets a copy of the master front end from the server and copies to their local profile creating their own unique front end. All users log off and on each day so each day they get the latest front end (or more often if they log off and on during the day which is what I prompted everyone to do after last C&R front and back end).