Database Maintenance and Kick Users

[vanzie]

Does anyone have a sample database where the admin can kick current users out while doing maintenance? or to notify the users that database maintenance is scheduled and have them locked out from using the database

 

[theDBguy]

Hi. Have a look here.

UtterAccess.com

www.utteraccess.com

 

[Pat Hartman]

It is far better to do your maintenance during off-peak hours when possible but you will still need code like this if your users have a habit of going home without closing the app properly.

 

[vanzie]

It is far better to do your maintenance during off-peak hours when possible but you will still need code like this if your users have a habit of going home without closing the app properly.

I've disabled the close button so the users are forced to log out first but they still leave the app open but the next users just continue on their account and I don't want that to happen

 

[CJ_London]

What maintenance are you referring to? forms/reports? data? table structures?

they still leave the app open but the next users just continue on their account

what does this mean? are users sharing the same machine?

 

[vanzie]

What maintenance are you referring to? forms/reports? data? table structures?

what does this mean? are users sharing the same machine?

No there are 3 different computers in that control room but they use the front end file on the network. So if user 1 uses PC 1 and goes off duty, user 2 comes in to use PC 1 with user 1's account still logged in

 

[vanzie]

No there are 3 different computers in that control room but they use the front end file on the network. So if user 1 uses PC 1 and goes off duty, user 2 comes in to use PC 1 with user 1's account still logged in

And if I should do maintenance, it will be to update a report or make small changes to some errors picked up. Maybe I found new code that I can implement in that app

 

[CJ_London]

better explain your setup - is the db split? and each machine has their own copy of the front end?

missed this comment

they use the front end file on the network

each machine must have their own copy of the front end - sharing a front end on a network is the way to corruption.

 

[vanzie]

better explain your setup - is the db split? and each machine has their own copy of the front end?

We use a network share drive. They log in on the same front end file but don't have access to the back end file.

 

[CJ_London]

the back end file is Access? or SQL server/something else?

I presume the network share is not terminal server or citrix? If not, you are seriously risking corruption - each machine needs it's own copy of the front end on it's local drive and needs to be hard wired to the server (unless using terminal server/citrix in which case the front end will be on the server, but again a copy for each machine in their respective profile directories)

Either way, by ensuring each machine has their own front end copy, you can work on a master copy and update a table in the back end to indicate there are updates available. The front end interrogates the table on a regular basis - might be a form timer, might be an event that happens regularly such as opening a form - and if it picks up there is an update, closes, copies the new front end to the local drive and reopens. Plenty of examples out there how to do this

 

[Pat Hartman]

We use a network share drive. They log in on the same front end file but don't have access to the back end file.

Just because multiple people do not normally open the app at the same time, is not a valid reason for not splitting the app. I split apps I make for myself because it is just that much easier to make changes to them.

Start by splitting into a BE (tables only) and FE (everything else). Access actually provides a tool on the menu to help with this but you can do it yourself also.

Once the app is split, move the BE to the shared network folder. Put a compiled and compacted copy of the FE in a separate folder on the server. Relink the FE so that it links to the tables on the shared drive on the server if it does not already do that. Then zip the FE and place the zip in a safe place that gets backed up. Put an unzipped copy of the FE in a separate shared folder. Create a shortcut for the user to use to start the app. The shortcut should run a batch file that copies the FE to the users local drive and runs it. I'll include a sample batch file.

Once this is all set up correctly, it won't matter so much if the user doesn't shut down when he leaves. You should still include timeout code just in case the user leaves in such a hurry that he leaves an edited record open without saving it.

If you need to make changes to the FE, do it on your own PC using your own copy of the FE. It is preferable to also make a copy of the BE and relink to the "test" BE so you can test your changes without worrying about modifying production data. Once you are certain that the changes are working correctly, go through the same steps I outlined above to replace the FE copy on the server. If you want people to use the new copy immediately, send them an email and tell them to close and then reopen the app with the shortcut. That will automatically distribute a new copy of the FE.

There's lots of other loose ends and details you may need to incorporate but that will give you direction.

Sample .bat file

md c:\DwgLog
del c:\DwgLog\DrawingLog.accdb
copy "\\BSCCTIMBERLINE1\Timberline Office\AccessApps\Data\CommonFE\DrawingLog.accdb" c:\DwgLog
c:\DwgLog\DrawingLog.accdb

 

[vanzie]

Thank you for your inputs, gents. I'm not as advanced in databases as you guys but I did read about splitting databases and put some of that info into practice but i'll work on a better one next time