Compact and repair backend after user exits frontend

[Datament]

I developed a multi-user application with 10 frontend users and tables linked to one backend. The problem is that the backend houses a couple of tables tbl_Acct and tbl_RTN with over two thousand rows each of data. When users are in their assigned frontends working the size of the backend increases to about 2MB and crashes the application.

I have updated the frontends and the backend by checking the "Compact on Close" box of each application which helps when monthend reports are running, but has a tendency to crash when multiple users are working simultaneously.

Is there anything else I can do to prevent crashing and to trigger the backend to compact itself when all the users have exited?



All suggestions and assistance with this situation would be greatly appreciated.

[ranman256]

you cant compact while users are in it,so turn off that auto compact event.

force all users out , usu with Computer Management ( i put in a kill switch that will shutdown the app for all users.)
once all users are out, then compact/repair

if you have different users with different Office versions and have office references in the code, then it does corrupt a lot.

[CJ_London]

A few thousand records is not much. I would look at what is causing the bloat

[Datament]

Are you saying to turn off the auto compact event for all the ten frontends and the backend copy? The frontends are all the same version but different users.

I have never used a kill switch and I;m not familiar with that process. I would welcome and appreciate more explanation on that.
Thanks much for your time.

[Datament]

Agreed, a few thousand records is not much, and I am still reviewing what may be causing the bloat and crash.
Thanks for your time.

[Micron]

Are you dealing with a lot of pictures or attachments?
There is more than one cause of db bloat - here's one.

[CJ_London]

2mb is not much either, max size for an access file is 2gb.

so assuming you dont mean 2gb I would be looking at all the processes the users use as I suspect the bloat is not the reason for it crashing. Do you get any errors? Can the BE be opened after crashing? Are all users locked out?

[Datament]

No Pictures at all.

[Datament]

The db rises to that 2gb when a few append and update queries are ran every morning to update the account tables for the 10 users.

Yes, the BE can still opens after one of the FE crashes,and NO not all users are locked out. Only the user that encounters an error message is locked out.

[Gicu]

I agree that you should look more into the cause of the bloat; please feel free to use my free utility which can be scheduled to run (using the built in Windows Task Scheduler) at your desired times:
https://forestbyte.com/ms-access-uti...end-compacter/
Cheers,

[CJ_London]

NO not all users are locked out. Only the user that encounters an error message is locked out.

if the BE has risen to 2Gb in size then I would have thought all users would have been locked out as the BE would be unuseable. If it only occurs with one user - look at that users installation. Sounds like Gicu's utility is well worth a try.

What is the size of the BE before users start their month end routine? And please be clear about Mb and Gb - the latter is 1000 times larger than the former. I have a BE with around 150,000 records, increasing by around 7000 records a month. Even with that number of records it is only 32Mb.

Would also help us to help you to understand what your month end routine actually involves - deleting data? importing data? manual entry of new records?, etc

[NoellaG]

It seems a bit like there are temp tables created in the front end application. Maybe the following article can help: https://www.experts-exchange.com/art...ft-Access.html

[Datament]

Size of BE before users start is 607,828KB. And it has a couple of tables with approximately 800,000 to 2,500,000 records each.

At the begining of every month there is a macro attached to a button which upon pressed will delete records of eight different tables and append new records to those tables. A couple of these tables typically houses 800,000 to 2,500,000 records during the month. No manual entry of records.

This application works smoothly when only one or a couple of users are in the frontends, but problems starts when the entire team of ten users are logged in at the same time.

I truly appreciate your time and continued assistance.

[CJ_London]

macro attached to a button which upon pressed will delete records of eight different tables and append new records to those tables

couple of questions

1. is it possible for more that one user to click the button, so repeating the exercise?
2. why delete the data? why not just import new data and update (if appropriate) existing records.

If these are effectively temporary tables from which only some data is extracted and stored, they ought to be in a temporary database that is created anew each time and linked to as required.

[NoellaG]

Tip: 10 simultaneous users is at my experience the limit when keeping the tables in access back-end. In your case I should switch to a SQL server (express = free) back-end. There you have a real database system with better options to check and optimize the database processes. Also you will have better security and ways to backup/restore your database. Also, in the SQL server there is always a temp database to hold temp tables, so no need to create one if you want to work with temp tables.

@CJ_London: truncate a table and upload the new records is nearly always much faster than deleting the obsolete records, updating the old remaining data and adding new records