Corruption and Maintainance (1 Viewer)

Ilovexfiles

Registered User.
Local time
Today, 05:47
Joined
Jun 27, 2017
Messages
37
Hello all
I have a multi user split database which has been working fine for about one year (users have a "copy" of the database that they use).

I have not done "compact and repair" or on close, as I don't fully understand it and have been reading mixed things about how it impacts the database negatively.

What is the best way to repair and compact and limit corruption on the database?


Thanks
 

Cronk

Registered User.
Local time
Today, 23:47
Joined
Jul 4, 2013
Messages
2,770
(1) Routinely run "Compact and Repair" especially on the BE
and
(2) Have backup copies of both the FE and BE, the latter daily if edits are made daily, and test the backup.


You can get the same result by copying all objects to a blank database but it's easier to use the inbuilt functionality.


"Compact and Repair" will eliminate bloat and re-create indicies.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
Adding to Cronk's advice, your multi-user environment often means that you must set aside a time on a regular schedule to perform maintenance. I.e. don't just assume you can do it; ASSURE that you can do it.

I polled my users to determine a good time, and then advised them that at a particular time on a particular day, the DB would be down for scheduled maintenance. During that time, I assured that nobody was in the database. I then MOVED the BE file to a working folder (drag-n-drop worked OK for this) so that nobody could come in during maintenance.

Once I started maintenance, the sequence was:
- Move the file
- Backup the file
- Other maintenance if required
- Compact & Repair
- Return the BE file to the working folder

"Other maintenance" occurred when (a) I added a new feature or (b) we had a known data problem due to user error and I had to untangle their mess.

As a side note, you are doing the sharing absolutely right by making the users run a "local" copy of the FE file. But if you are worried about FE "bloat" then look on this forum for suggestions on how to launch a script that copies the latest/greatest FE from the shared location and then launches it. Then you C&R the FE, too. That way, everybody starts with a clean FE every time - and if you have posted a new FE version, they get it at the next shot.
 
Last edited:

Ilovexfiles

Registered User.
Local time
Today, 05:47
Joined
Jun 27, 2017
Messages
37
I just did compact and repair of by BE and now I have a second copy of each table, so i have two of each table. One set is linked the other isn't.

Is this normal
 

BeeJayEff

Registered User.
Local time
Today, 05:47
Joined
Sep 10, 2013
Messages
198
No, that's not normal. What do you mean when you say that one set is linked ? They should not show as linked in the BE.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 19, 2002
Messages
42,970
Carrying on with Doc's suggestion --- C&R should never be done on a database on the server from a workstation. You need to log on to the server itself to do the CR so Jet/ACE doesn't have to work across the LAN. If you don't have permission to log into the server itself, the next best thing is to copy the BE from the server to a local directory. CR and then put it back on the server. Renaming the BE while this is going on is a good way to prevent users from being able to open the qpp while the CR is in progress. When the CR is complete, move the renamed version to the back up folder and replace it with the compacted version.
 

BeeJayEff

Registered User.
Local time
Today, 05:47
Joined
Sep 10, 2013
Messages
198
I don't understand that, Pat - what's wrong with having Jet/Ace working across the LAN ? And if you leave the BE accessible to users while you take a copy to C&R it, then presumably you will lose any changes the users make in the meantime.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
I'll step in on this one.

what's wrong with having Jet/Ace working across the LAN ?

1. EVERY action on a "true" Access BE file is performed by the copy of Access on the workstation used for the operation, which I will call the FE station NOT to be confused with the FE of the application.

Normally, you don't install Office on a server. In particular if you follow network design guidelines published from various sources including federal government guidelines for server farms, a "division of labor" concept means you MAY NOT (as in NOT PERMITTED) install an app on a general back end file server. So you CANNOT do the C&R or other maintenance on the BE server. (Which is why an SQL Server is not usually on the same system that acts as a file server.)

2. IF you are running Access on an FE workstation but are compacting a BE file, even when operating stand-alone (i.e. the FE app isn't active), you are using Server Message Block protocol over the network to read the contents of the BE piecemeal AND to write back the compressed info the NEW BE file, because C&R works that way.

However, if you do a file copy to the FE station and do the C&R on that local copy, you are using straight disk I/O. You eliminate the network as a middle-man. That's TWO file transfer steps you eliminate - SMB from BE (old file) to FE and then SMB from FE to BE (new file). AND you still have disk I/O to read the old file and write the new file anyway. So only TWO I/O steps instead of four.

3. Working on a local copy means your BE copy is relatively safe on the BE server and you are working on a COPY of your critical data.

So if there is a WHOOPS, no biggie. Make another copy of the BE from its safe place and do it again. That safety through isolation is based on (a) a different machine with a different hard drive, which now requires a double bad event that takes down TWO different disk on two different machines in order to lose data, and (b) not being subject to the vagaries of network "gotcha" events.
 

BeeJayEff

Registered User.
Local time
Today, 05:47
Joined
Sep 10, 2013
Messages
198
OK, I can understand that - thanks. Do you prevent other users from connecting to the BE while you're doing this, and if so, how - a simple maintenance mode flag set and checked maybe ?
 

Mark_

Longboard on the internet
Local time
Today, 05:47
Joined
Sep 12, 2017
Messages
2,111
Rename the back end file to ".OffLine"
Copy the ".Offline" file to your local machine and rename it to its original name
Compact and repair your back end file
Copy your back end file WITH proper extension back to your server
MOVE the .Offline file to an archive location
RENAME as needed for your backup routine. This will normally mean you either change the file name, file extension, or BOTH so that you are very aware of what is valid as of when.

This also means that, as you've changed the file extension FIRST, no end user will be able to access the system while you are doing this.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
I used a variant on what Mark_ said. I just did a drag-n-drop of the file to a working folder which effectively cut off the FE files from finding it. That was my "safe place" copy on the BE server. I made my FE system working copy for C&R and other types of maintenance from there. When I was done, I copied the fresh version directly to the expected folder and then to the working folder. Gave me a chance to do a backup off a clean copy. Since we had automated tape backups in place, I waited 24 hours and then tested whether the new copy was now on tape. EVENTUALLY, that secondary working folder would get cleared out.

Just remember: The FE doesn't browse for the BE file at app launch time. It is either where it should be or there is no BE to be referenced. So a path change OR a name change has the exact same effect. Users can't get in until you make the BE file exist where it was expected.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:47
Joined
Feb 19, 2002
Messages
42,970
I started copying the BE to the local PC at least 20 years ago, mostly because compacting a database of any size over the network was simply slower than molasses. Whereas copying, compacting, and copying back was significantly faster. Recently though I helped someone who actually lost data in the BE when his network connection failed. I had never considered that but apparently it is possible. I suppose it just depends on what Access is trying to do when the connection fails. So add that as a second reason to not compact over the network.

I use the method Mark suggested of renaming the BE so the FE won't open (you can give the user a meaningful message if the BE is "missing").
 

Mark_

Longboard on the internet
Local time
Today, 05:47
Joined
Sep 12, 2017
Messages
2,111
@ Pat,

Also means you KNOW if someone is still in since you won't be able to rename. At least you won't if the back end is on a proper server! :)
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:47
Joined
Feb 28, 2001
Messages
26,996
Mark_ - the rename fails even if it is NOT on a server, because the Windows Distributed Lock manager concept says the file's true host maintains the locks. So if there is any kind of Windows on the system, OR a UNIX box running SAMBA so that it can be a Windows File Server, the locks will be there.
 

Mark_

Longboard on the internet
Local time
Today, 05:47
Joined
Sep 12, 2017
Messages
2,111
@ Doc,

Exactly. Can't remember which one, but there was an OS you could get back in the 80? 90? that would let you rename files even if they are being edited. Can't remember where I ran into this before. Very long time ago now.
 

Users who are viewing this thread

Top Bottom