How to make Database that is open 24/7 faster and smaller?

[gemma-the-husky]

Well as I am not a veteran to handling an active access database I am not sure what "Maintaining" a database would entail.

I have not supervised one over a lengthy amount of time to know what would need to fixed or "maintained".

Assuming that my database is stable and won't crash then I don't see any other problems occurring apart from bloating.

The term "maintain" is very broad so if you'd like to explain what exactly maintaining a database would be then maybe I would be able to tell or not if I am out of my depth.

Well maintaining a database includes dealing with users who report an error, resolving the error and if necessary fixing the database so it doesn't fail again for that issue. It also includes modifying the database to provide additional features requested by users. Hence the developer/service engineer needs to understand the database, and have practical skills most likely honed by many years of doing such work.

 

[omarrr128]

Well maintaining a database includes dealing with users who report an error, resolving the error and if necessary fixing the database so it doesn't fail again for that issue. It also includes modifying the database to provide additional features requested by users. Hence the developer/service engineer needs to understand the database, and have practical skills most likely honed my many years of doing such work.

Thank you for giving a precise reply. But let's say the system is stable and does not crash? And also does not require any new features?

 

[The_Doc_Man]

But let's say the system is stable and does not crash? And also does not require any new features?

As long as you have bloat, there is a significant chance of a catastrophic problem. Depending on the discipline of your user base to properly log off after use, there are avenues for problems to creep in. It would be very rare for a live, multi-user database with bloat issues to NOT crash. At least, in my experience. And I've been using access for well over 20 years.

 

[gemma-the-husky]

Thank you for giving a precise reply. But let's say the system is stable and does not crash? And also does not require any new features?

Well I don't know what windows services you use, but what if there's a network issue and a particular drive goes off line, or the internet stops working. Maybe you use FTP services, and something changes so FTP no longer works. There might be a windows update that cause Access to fail. We have had a few doozies of that nature. Who knows?

 

[JeanMarc22]

Hi omarrr128. I had a similar situation. I won't repeat all the good advices above. My solution was this: when users click on the link to start the database it calls a script that copies the frontend to the users' profile. This way you can continue to develop and you deploy to a hidden folder where your script will pick it up.

As for the backend, because it's used 24/7 the only good move is upload it to a SQL server. There are good free ones: Postures and MySQL.
I don't have a 24/7 issue but I needed to do regular compact/repair/backup. So, in the front end I created a hidden form that is called on start up. It has a timer event set to 5 minutes. The code behind checks for the existence of a text file where the backend reside. If file exist it warns user to exit the database in the next three minutes, or prevents the frontend from starting up, with a 'maintenance' message.
And for speedy compact and repair I bring the backend to my desktop, compact/repair, upload the backend, delete the maintenance text file, then rename my copy to add a date and archive it elsewhere. All this by script. But wait untill the backend's lockfile is gone.

 

[omarrr128]

Hi omarrr128. I had a similar situation. I won't repeat all the good advices above. My solution was this: when users click on the link to start the database it calls a script that copies the frontend to the users' profile. This way you can continue to develop and you deploy to a hidden folder where your script will pick it up.

As for the backend, because it's used 24/7 the only good move is upload it to a SQL server. There are good free ones: Postures and MySQL.
I don't have a 24/7 issue but I needed to do regular compact/repair/backup. So, in the front end I created a hidden form that is called on start up. It has a timer event set to 5 minutes. The code behind checks for the existence of a text file where the backend reside. If file exist it warns user to exit the database in the next three minutes, or prevents the frontend from starting up, with a 'maintenance' message.
And for speedy compact and repair I bring the backend to my desktop, compact/repair, upload the backend, delete the maintenance text file, then rename my copy to add a date and archive it elsewhere. All this by script. But wait untill the backend's lockfile is gone.

Does uploading the backend to an SQL server mean I need to install extra software? Unfortunately I can't do that if so.

What would happen if the user doesn't exit the front end when the code shows? Is it able to kick them out?

 

[The_Doc_Man]

If you don't have an SQL server already set up (or some other major database package like ORACLE or SYBASE or INFORMIX) then yes, we are talking about adding extra software. Unfortunately, Access is not as robust as some other packages and therefore is more prone to issues that would require shutdowns and repairs. It is certainly NOT a non-stop 24/7 product. It will work hard for you - but it is a low-end database that will run into its limits very quickly if neglected.

In general, if you CAN run code behind the scenes in an Access database and you ARE planning a shut-down, there are things you can do to each user that would have the ability to force them out. It starts with a way for you to signal the software that a shutdown is scheduled. I did this with a simple table that had start and stop date/time and a description of WHY the shutdown was scheduled. I also had a switchboard or control panel setup where one form was always open. That form had a timer routine that once every 30 minutes, checked for the list of shutdown events to find the next shutdown. When that shutdown was less than 6 hours away it changed to every 10 minutes. When it got within 30 minutes, it switched to 5 minute testing. During that "countdown" the tests would pop up a message box with info about the coming shutdown. Other things were needed, too, like testing each open form for being "dirty." IF it WAS dirty, I took steps to clean it before issuing commands that would shut down all forms. With due diligence for potentially open recordset issues, an Application.Quit was usually pretty good as a way to shut it down.

 

[omarrr128]

Hi omarrr128. I had a similar situation. I won't repeat all the good advices above. My solution was this: when users click on the link to start the database it calls a script that copies the frontend to the users' profile. This way you can continue to develop and you deploy to a hidden folder where your script will pick it up.

As for the backend, because it's used 24/7 the only good move is upload it to a SQL server. There are good free ones: Postures and MySQL.
I don't have a 24/7 issue but I needed to do regular compact/repair/backup. So, in the front end I created a hidden form that is called on start up. It has a timer event set to 5 minutes. The code behind checks for the existence of a text file where the backend reside. If file exist it warns user to exit the database in the next three minutes, or prevents the frontend from starting up, with a 'maintenance' message.
And for speedy compact and repair I bring the backend to my desktop, compact/repair, upload the backend, delete the maintenance text file, then rename my copy to add a date and archive it elsewhere. All this by script. But wait untill the backend's lockfile is gone.

Are you able to give an example of the script that would copy the front end to the users profile?

 

[The_Doc_Man]

Search this forum for "auto updater" - and the search allows you to put in a name as a type of filter. Pat Hartman has several posts on the subject as she has used this method for quite a while.