Hi,

I have a Front End and a Back End located on a machine in the Central Control of a Prison that is in operation 24 hours a day 7 days a week. There are also other Front Ends with different functionality placed in different buildings / offices on the LAN at the facility.

Approximately 10 minutes before the end of each shift, the officers will press a button on a form in their front end to initiate compacting / repairing and backing up the Back End.

With research, I've found several different compact and repair methods:
Set oEngine = CreateObject("JRO.JetEngine") that uses oEngine.CompactDatabase

and another method: Application.CompactRepair(

There may be others. Is one superior to the other?

On the front end that the operator initiates the Compact / Backup procedure, I can close all of the Open Forms with the exception of the Modal Unbound Form with the button ensuring that nothing on the front end will do any reads / writes or be connected to the Back End with a Bound Form.

My current plan, using VBA is to:
1) Make a temporary copy of the Back End (in case something goes wrong) using the FileCopy routine (is there a better alternative?)
2) Perform the compact / repair (Source / Destination) using one of the methods above.
(Upon determining a successful compact/repair)
3) Kill the Source and copy the newly compacted Destination back to the Source Name.
4) Kill the temp copy.
-- Optional then copy compacted version naming it with a time stamp to another folder and to a Folder on another machine on the Network.
5) restart the main form of the database and close the Modal Form in the Front End.

None of this seems to be daunting until I consider the external Network users using front ends on the network.

Unless I've missed something above this is the part that I have the least amount of knowledge about and need the most help with.

What steps must I take to ensure that another user isn't accessing the Back End? Central Control's Machine has one form bound to a table. The rest of the Forms are unbound. Other Front Ends, however, have Forms Bound to Queries. Is that the same thing as being Bound to a linked Table?

I am assuming that whatever the solution for Network Users has to be done before step #1

So, recommendations anyone? Can I open the Back End exclusively from the Front End?

How do I unload other users? or lock them out. Someone could have left a FE open and walked away or they have gone home that could possibly cause a problem; preventing the compact operation.

Did I leave out any significant steps?

Thanks!

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

Not sure if it answers any of your questions but take a look at Brent's sample code here.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

>> What steps must I take to ensure that another user isn't accessing the Back End? <<

Use the utility name Who's Connected and enable passive shutdown. By doing this, you will prevent any NEW connections to the database -- so monitor the file until you are the only one connected, then close the utility, then run your backup routine from your Front End.

Here is a sample ....



You can use CloseAllForms before you all BackupAndCompactBE, then you can call your startup code after the routine is done.

One of our admins (BananaRepublic) has just released custom code for doing just what are seeking to accomplish.

http://www.UtterAccess.com/wiki/index.php/...ight-click_Menu

hope this helps

Thanks for the reply.
Sorry, I thought that I had selected 2003 in the version drop down.

Thanks for the reply. I had seen FileCopy in one of the examples on the net from a google search. I actually have some places where I have use the Scripting.FileSystemObject because I deal with pictures. I use that to copy them from the camera to a location on the system.

This post has been edited by wizarddrummer: May 15 2012, 11:44 AM

Thanks very much for the reply and I am sure that that solution is something that will help many, but it does not accomplish what I need.

1) It requires Registry Entries (not interested in solutions with that overhead on potentially 100's of installs)
2) It's creating Zipfiles. (also something I don't want)
3) It does not answer the question about Network Users.

Hi,


For future reference, if you "preview" your post before submitting it, you will have to select the version number again, or it will not show up.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Have you tried the frame work I have laid out? I use it all the time and it seems to address your needs.

PS> I missed that DB had linked to a previous post of mine that does something similar. (IMG:style_emoticons/default/dazed.gif) The file created from the code DB linked you to is a back-up, so the 'working' BE is not compacted --- the working file is backed up, then compacted and remains a static file. Where as, the code I posted in this thread is intended to make a back-up and compact the 'working' BE.

THANKS for the reply.

I checked out the Utility. Yes it does show connections, but I was unclear about what the Passive Shutdown is supposed to do. It didn't appear to do anything after I checked the box and I waited more than 15 minutes.

This system does not require users to log in with passwords. The Front Ends only have linked tables in them.

Also, in trying to make this functionality "part" of the system, so that End Users simply initiate it, there won't be anyone to open up the Who's Connected Utility to "monitor" anything. In a Prison you have various buildings: Central Control, Admin, Medical, Kitchen, Warehouse, Units, Dorms, Armory, Fire & Safety, etc., all with staff members using various front ends to do their tasks.

As in any setting you have people away from their computers for many reasons.

I'm going to ask new related question in a new reply.

This post has been edited by wizarddrummer: May 15 2012, 02:50 PM

>> I checked out the Utility. Yes it does show connections, but I was unclear about what the Passive Shutdown is supposed to do. <<

The passive shutdown will PREVENT NEW connections to the database, however, it won't kick them out. In order to kick them out, what I do is have a hidden form that opens upon start-up, then in the Timer event check the status of the passive shutdown property by seeing if a new connection object can be opened, if it cannot, then I shut down the front end. I typically will open a Form object with a message on it that says the app is going to shut down soon -- then 30 seconds later -- I toast 'em ... (IMG:style_emoticons/default/evilgrin.gif) mwahahahahaha!!! ....

There are other techniques that are quite adequate ---

- checking for idle time, then shutting down
- I describe another, very similar, technique here.

There are other threads on the topic of "kick out users" if you want to search out more of them.

These questions are related to my original question.

Lets break this up into two sections.

1) Compact / Repair.
I get it. A compact and repair can not take place if the database is opened exclusively by some other user.

I can work that part out. That can be done at 1:00 AM when most staff members are not there.


2) BACK UPS

Can a BACK END simply be copied using (Explorer or FileCopy or Filescrpting object) without any problems. I've copied Back Ends on my development machine with no ill consequences.

Seems to me that the "copy" would just be that. A copy of whatever data was in the Back End at the time.

95% of this system is database reads. The writes only take milliseconds to perform because of small data sets (done with unbound forms using VBA)

Compacted the Back End is about 10 MB. Even after 10 years of use the system will probably only be (after compacting) about 20MB at most.

This is a low volume database. The maximum number of Offenders is limited to the number of beds the facility has.

There is only one table, an historical table, that is a transactional log of activity. When an offender is moved within the facility or transferred or discharged that information (DOC, Name, From , To, Date/time, etc.) is stored. The database that I wrote 11 years ago that I am "retiring" with a much more improved version has 17, 800+ movements since 2005. Records in that table prior to 2005 were deleted which is why the size is only 10MB.

So, my question is this:
When copying a Back End to another folder do I still have to worry about exclusive users being connected?

I toast 'em ... (IMG:http://www.UtterAccess.com/forum/style_emoticons/default/evilgrin.gif) mwahahahahaha!!! ....

I LOVE IT!

THANKS for that, I understand a lot more now. I can probably scavenge some of that code and put that into the Central Control's front end to block new connections. I figured that after 15 min of nothing that I would have to come up with or adapt something to create some (IMG:http://www.UtterAccess.com/forum/style_emoticons/default/evilgrin.gif) mwahahahahaha!!! functionality that will work.

Great stuff!

Just curious.

I have one timer that I use that currently checks to see if it is shift change so it can write a facility count record (movements have to be tracked by shift)

Is it typical for a system to have a lot of timers running in it? I know that that is an open ended question that would get the response of "depends on the application", I get that. But, from a design perspective, any tool can be over used. I'm just wondering if anyone has an opinion about using timers and how often and how many.

This post has been edited by wizarddrummer: May 15 2012, 06:43 PM

Hi Wizard,

Glad to hear you got it to work! Good luck with your project.

>> 1) Compact / Repair.
I get it. A compact and repair can not take place if the database is opened exclusively by some other user. <<

I would re-phrase that ...
"A compact and repair can not take place if the database is opened exclusively by some other any user."

>> 2) BACK UPS
Can a BACK END simply be copied using (Explorer or FileCopy or Filescrpting object) without any problems. I've copied Back Ends on my development machine with no ill consequences. <<

While you have not experienced no ill consequences, that does not mean the process is impurvious to failure! But ... the direct answer is that FSO can copy an open file, but VBA.FileCopy cannot.


So, with the code I wrote that DB linked you to (here), I used FSO to copy the Back End, then the code compacts that back up file. With that sample, I use FSO intentionally because my intent was to copy an opened file, then compact the copy. The code I wrote in this thread in post#3 uses a slightly different paradigm, it re-names the working back end to reflect that of a backup file (and it is uncompacted), then use the CompactDatabase method to compact that newly minted backup file to a new working back end file. That technique works only if the assumption that the working back end file is connection free (ie: closed).

>> I toast 'em ... (IMG:style_emoticons/default/evilgrin.gif) mwahahahahaha!!! ....
I LOVE IT! <<

(IMG:style_emoticons/default/thumbup.gif)

>> Is it typical for a system to have a lot of timers running in it? <<

I typically have at least one with a multi-user application -- the one that checks to see if the app needs to be shut down. I will use more if I need them. For example, I have a Form object called frmHeartbeat and in the OnTimer event I will create some that looks something like this:



I then have design my TimerInterval to reflect that of the smallest heartbeat, so in this case it would be 30,000 ms.

Timers are cool, but I use them sparingly because Access is a single threaded application, so if a timer is running, nothing else is. I typically have my shutdown heartbeat for about 5 minutes, and I cannot think of a situation in which I created a heartbeat with a frequency of any smaller than 30 seconds. One thing that is important to note is that if you have 15 users concurrently connected, then you are firing your timer 15 times for each heartbeat, so if the task is data intensive, I would suggest that you create a separate "heart beat" application that runs as a single instance on a server or your PC so the Back End is not hit so heavily.

With regard to compacting regularly, How about setting the BE property to compact on close. That way each time the last user to use your application closes it, the BE database will compact automatically upon closing.
I am not sure this is how it will work. Particularly since you wrote that you are using unbound forms. I am contemplating this method myself.

I hope someone here can clarify if I am correct. Will this setting have some other effects like the speed of response, or the speed of the FE shutting down?

Its important to realize that Access is comprised of two components, the application UI and a database engine. With that, some properties we set through the ui are for the ui, and some are for the database engine. The compact on close property is for the ui, not the engine, therefore only effects the current file opened by the ui, which is the front end in a split configuration. Since a backend database is opened by the engine for its data, setting the compact on close for that file will have no effect, unless the back end is opened by the UI.

Does that make sense? (sent from phone, thus the brevity of the reply)