Compact and repair backend after user exits frontend

[Datament]

1. It is possible, but highly unlikely for more than one user to click the button at the same time.
2. Begining of every month we delete already processed data, and append new records.



That makes sense, never thought I could have more than one BE in Access. To be clear, are you suggesting that I create another Backend DB with the temporary tables and link the FE to it?

[NoellaG]

To be clear, are you suggesting that I create another Backend DB with the temporary tables and link the FE to it?

No I would say: stay with 1 backend, but create it in SQL Server Express (or if you have a budget: SQL standard) or PosgreSQL (there are other options) and not as native Access tables. They take automatically care of the tempdb problem and you can have 50 or more simultaneous users. You can link your existing Access FE app easily to the new BE.

[CJ_London]

10 simultaneous users is at my experience the limit when keeping the tables in access back-end.

with a properly designed and efficient access BE I have had apps with 60 users. Not necessarily all simultaneous but certainly more than 10 active at the same time.

@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

but causes significant bloat in Access - in many cases, only necessary to append new records since old ones don't change. Depends on the type of data - if transactional (e.g. invoices) then old ones don't change, if informational (e.g. customer details) some data might change. And I would argue the only reason for deleting data is for archival purposes - records that are no longer relevant should be flagged as such. A customer is always a customer, even when they cease to be one. Delete the customer record and you have to also delete all the related data such as invoices, payments etc.

Begining of every month we delete already processed data, and append new records.

To be clear - you are importing completely new records, never seen before?

It is possible, but highly unlikely for more than one user to click the button at the same time.

I didn't mean at the same time, I meant more that once during the month end process, might be a couple of minutes apart might be a day. You should ensure that once data has been uploaded, it cannot be uploaded again for that month. If it takes time to upload, no one should be able to do anything until the upload process is completed.

I could have more than one BE in Access. To be clear, are you suggesting that I create another Backend DB with the temporary tables and link the FE to it?

yes you can. The only downside is you cannot set relationships (joins are fine) between tables in different BE's. But from what you are saying, this would not be relevant anyway.

You can use vba to create a new db, create the tables, indexes and relationships, or perhaps simpler is to create a template db with the required tables, indexes and relationships and use vba to copy this each month using filecopy to overwrite the old copy. In your FE you would have linked tables to the copy. If, as I am suggesting, your copy is always named the same, then there is no need to relink

edit- note if other users have an open link to the copy, it will not be possible to delete the old copy. Users (including the one clicking the button) don't need to be out of the FE, they just need to not have a form/report/query open that references a table in that copy