Is there any way to detect or skip Access's last run failed Compact and Repair dialog?

[bxdobs]

Background:

Several MDB Apps developed over 2 decades starting from; Office 97, and progressing through 2000, XP, 2003 and now 2016 ... up until switching to 2016 on Win X, these apps have been fairly stable ... recently they have been plagued with corruption events usually as a result of updates with MS WX auto restarting. All these apps have a FE monitor that allows for remote (ADMIN) shutdown so recently added a scheduled shutdown feature which appears to have quieted down users complaints until today (Note: these apps must remain in MDB format to allow use from machines still licensed with Access 2003)

Configuration:

Intranet: 10 local + 3 vpn with mixture of Win 7 and Win 10 machines (MAX connections to any given app never exceed 6)

Windows 7 Pro 64b machines are licensed with (Office Pro) Access 2003

Windows 10 Pro 64b machines are licensed with (Office Pro) Access 2016 (32b installed for mdb application compatibility)

MDB's with:
BE on shared network drive
FE on Local Machines with copy on shared network drive
Admin BE on shared network drive
all tables are Linked to BE

Scheduled Nightly Auto Shutdown issued to all running apps (all running FEs monitor an admin BE for remote shutdown requests) ... this allows backups and MS updates to proceed without bumping in to open applications

Issue:


This morning, even though one particular application started fine on multiple machines, one of the machines would not start until it completed a Compact and Repair which failed because C&R requires EXCLUSIVE rights (not possible because other machines were already running the app) ... recovery required system wide shutdown to all running apps, allowing Access to do the C&R on the offending machine then restarting the application on the other machines ... users notably upset with interruption

Looking for better Solution(s):

Suspecting the local FE became corrupted for whatever reason(s). Regardless, could overwriting the local FE with the network copy of the FE be a potential solution ... if so, would this have to be done at every startup OR is there some way to trap this C&R error? Perhaps there is a way to query the state of an MDB externally and on error copy over the FE?

OR

Perhaps there is some Windows API available that could be monitored for pending MS UPDATE Shutdown requests that could be used to trigger the apps shutdown logic

Other ideas would certainly be welcome.

Recently started testing reLink Logic to help resolve the biggest issue with Networked BEs ... specifically ... if the shared machine reboots or the network is interrupted while apps are open it causes all open apps to become unstable and potentially corrupted ... this machine has been configured for manual updates PLUS it as well as all the associated network gear are on a huge capacity UPS ... SO ... the only way it reboots is if it fails, encounters an extended power outage OR someone physically restarts it. Essentially, the relink logic is supposed to periodically test links to the BE ... if the link fails the application is supposed to gracefully shut down ... this logic is still in development

[bxdobs]

Update:
Ultimately, I would prefer users NOT get CORRUPTION MESSAGES ... HOWEVER at this point ... just wrote and tested this work around

When user encounters a MDB Failure to Start message, they will now be directed to run an Administrator Script which does the following:

repair.bat (with shortcut set to run as administrator)

REM kills ALL OPEN ACCESS parent and child processes (typically, if one app is having issues, they all do ... so ... this closes everything to do with Access)
killtask /im msaccess.exe /f

REM remove stray ldb file(s) (add for each app involved)
if exist <LocalAppPath>\<AppName>.ldb delete <LocalAppPath>\<AppName>.ldb

REM copy Network Released Version(s) of FE over Local FE copy (add for each app involved)
copy /y <NetAppPath>\<AppName>.mdb <LocalAppPath>

REM forces complete recompile of FE to ensure compilation is done in local Office Version (In our case; 2003 and 2016) (add for each app involved)
<LocalOffice32Path>\msaccess.exe /decompile <LocalAppPath>\<AppName>.mdb

REM optionally restart Application(s) (Note: Access may still ask if you want to open this file ... just answer yes)
<LocalAppPath>\<AppName>.mdb

[RuralGuy]

Do you have some version of http://www.accessmvp.com/JConrad/acc...e/kickoff.html installed to automate the remote shutdowns?
I have a hidden form loaded at startup that checks for shutdown about every 10 seconds.

This morning, even though one particular application started fine on multiple machines, one of the machines would not start until it completed a Compact and Repair which failed because C&R requires EXCLUSIVE rights (not possible because other machines were already running the app) ...

That quote bothered me a bit. Do you have more than one machine sharing a FrontEnd?

[orange]

Good question Allan --I wondered the same ---are these shared front ends?

[bxdobs]

Thanks for all the replies ...

Answer to the first question (RE: do I have an mvp app running to do shutdowns) ... Not using 3rd party S/W ... I had also added a hidden Form to the FE that monitors an admin app BE every 5 seconds for an Application STATE change ... if the STATE changes to Shutdown, the FE is supposed to do a graceful shutdown ... the Admin App/BE is something I wrote and implemented several years ago ... the only change made recently was to add a Scheduled Auto Shutdown Time so that all running apps are gracefully shutdown before backup (backing up active MDB files may introduce issues if the backups are reintroduced in to live operation)

Answer to the second question (RE: Shared FE) The original design/deployment (which I had documented and was using as reference for my post) was all FEs are copied to, and run from, Local Machines ... Taking a step back and PHYSICALLY looking at the CURRENT configuration, I can confirm that this particular App FE has migrated back to being deployed from its SHARED BE location (MEANING, YES IT CAN NOW BE OPENED BY MULTIPLE MACHINES ... WHICH, AS INFERRED, MAY INDEED BE PART OF THE CORRUPTION ISSUES) ... There were a number of full stop issues that I had to scramble to fix/patch with the introduction of Win 10 64b and Office 2016 32b to this network several months ago (MDBs are incompatible with Office 2016 64b which can only install as either ALL 32b or ALL 64b and can't coexist with any other version of Office) ... it certainly appears I will need to go back to review and update my migration documentation ... expecting one of the full stop issues had to do with launching this app

All machines (WX & 7) use a shared toolbar, linked to a folder of shortcuts, which is applied to their task bars ... this particular app is launched from this shared Toolbar ... to my recollection, Win 10 was having issues launching some programs from shortcuts ... expecting that I relocated this App to the BE as an interim solution until I found a solution (a task which has obviously fallen off my radar) ... strange that this app hasn't had any major issues until yesterday.

Regardless of the Corruption issue(s) ... does anyone know of a way to programmatically determine if the MDB requires a C&R without Users being presented with STRESSFUL DATA CORRUPTION ERRORS? There appear to be several errors that can occur (listed in numerous Repair Forums) but these all appear to occur before an app even opens making it impossible to use the Form_open() Event to trap these errors ... perhaps another app has to be opened that can attempt to load and or query the status of the MDB we want to open ... if it fails to open then the work around procedure listed in my second post can be run.

Currently Reviewing the Crystal Code Documenter Logic to see if there is some logic I can harvest to test this idea further. The Crystal Code Documenter appears to open and query both Code and Schema Level details of both MDB and ACCDB files. I will also need to capture one of the FAILED FE instances in order to test any logic.

While reviewing documentation for this reply, I located a VBS that I started writing several years back that may help address the FE startup problems ... the VBS would be responsible for locating and running the FE basically bypassing the WX shortcut issues ... including an isMDBCorrupt function to this VBS would ultimately be the cats meow.

Hmmm ... I think I recall seeing an Office Rollup Update recently ... I need to verify all machines are in sync with that release. Because the FE is currently incorrectly shared, any differences between Office software updates might explain the current instability. Tonight after business hours, I will also test replacing the current FE Shortcut with a VBS that should allow putting the FE back were it belongs.

[RuralGuy]

I am going to assume you will post back if you need additional assistance. Have fun.

[bxdobs]

Thx ... recycled some ancient BAT code (from another lifetime when command line ruled the day) and linked as Shortcuts to load the local FEs which included Version clause logic such that if the local version doesn't match the Current FE Release, a new FE would automatically be copied with the Compiled Code removed ... Plus created a Brute Force Shutdown Script that shuts down all instances of Access, clears any stray ldb files then reloads fresh copies of all FEs with Compiled Code removed ... these BAT Scripts should cover the majority of the WX startup issues I was having during our summer migration (which led to the Shared FE situation) ... BUT ...

Still would like to have a programmatic way to determine when a MDB or ACCDB file is going to want a C&R without involving the USERS (stop these Unwanted messages being presented to USERS). In 20+ years of using Access, I have only had one occasion where an MDB file had to be recovered using 3rd Party Tools ... using daily backups in a 7 day rotation, pretty much covers most hickups ... SO ... would be REALLY GREAT if this LAST real PITA could be put to bed.

[bxdobs]

OK for future reference ... when an MDB and or ACCDB file does a full stop on start up (meaning ACCESS refuses to open until Repaired via the Compact and Repair) it turns out these files will also not open using an ADODB.Connection request ... with that knowledge, I bashed together a VBScript (shared below) that can determine if the files are ok before actually attempting to open the files ... if the files fail to open, the VBScript deletes a revision control file ver.txt ... the appstart.bat file (also shared below) will refresh the FE copy when the ver.txt file is missing ... these 2 processes in combination can bypass ACCESS's annoying habit of providing Users Scary Startup Messages which usually requires administrative support ... missing from these shared files are parameter error checking and logging details which log history; date, time, machine, and user specific for any failure.

Hope these scripts can be useful for others.

Script: AppStart.vbs
Usage: %windir%\SysWow64\wscript.exe <AppStartPath>\AppStart.vbs <Server Path> <Application Path> <Application Name>
Note: the SysWow64 version of wscript.exe is required when working with MDB files (32b)

Option Explicit

Class ADOConnection
Public Function DBConnection(chConnect)
On Error Resume Next
Dim oDBC

Set oDBC = CreateObject("ADODB.Connection")

' if Open Fails, Resume Next will trap error
oDBC.Open chConnect

If Err.Number = 0 Then
DBConnection = True ' The DB File can be opened
oDBC.Close
Else
DBConnection = False ' The DB File won't open
End If

Set oDBC = Nothing
End Function
End Class

Dim oArgs
Dim oFSO
Dim chSrvPath
Dim chAppPath
Dim chDBFile
Dim chVerFile
Dim oDelFile
Dim chConnection
Dim oDBO
Dim loResult

Set oArgs = Wscript.Arguments

chSrvPath = oArgs(0)
chAppPath = oArgs(1)
chDBFile = oArgs(2)

Set oFSO = CreateObject( "Scripting.FileSystemObject" )

chConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & chAppPath & chDBFile & ".mdb"

If oFSO.FileExists( chAppPath & chDBFile & ".mdb" ) Then

set oDBO = New ADOConnection

loResult = oDBO.DBConnection(chConnection)

If loResult = False Then

chVerFile = chAppPath & "ver.txt"

If oFSO.FileExists( chVerFile ) Then

Set oDelFile = oFSO.GetFile( chVerFile)
oDelFile.Delete
Set oDelFile = Nothing

End If
End If

Set oDBO = Nothing
End If

Dim oShell
Set oShell = Wscript.CreateObject ( "Wscript.shell" )
oShell.Run(chSrvPath & "appStart.bat " & chSrvPath & " " & chAppPath & " " & chDBFile)

Set oShell = Nothing
Set oArgs = Nothing
Set oFSO = Nothing


Batch: AppStart.bat
Usage: AppStart.bat <Server Path> <Application Path> <Application Name>
Note: This Batch is currently only set up for mdb files ... it would require minor mods for ACCDB files

@echo off
:: appStart srvPath AppPath AppName

:: if dst ver.txt is missing then update
@if not exist %2\ver.txt goto update
@if not exist %1\ver.txt goto appStart

:: if the ver.txt files are not identical in srv and app paths then execute the update
@fc /b %1\ver.txt %2\ver.txt > nul

:: if comparison passes skip update
@if not errorlevel 1 goto appStart

:update
@if not exist %2 mkdir %2

@if exist %2\%3.ldb delete /y %2%3.ldb

@copy /y %1\%3.mdb %2

:: need to remove Office Version Compiled Code (Office 2003 and 2016 included here ... other versions will require additional lines)
@if exist c:\progra^2\micro~1\office16\msaccess.exe c:\progra~2\micro~1\office16\msaccess.exe /decompile %2\%3.mdb
@if exist c:\progra^2\micro~1\office11\msaccess.exe c:\progra~2\micro~1\office11\msaccess.exe /decompile %2\%3.mdb

:: update dst ver.txt file
@copy /y %1\ver.txt %2

:appStart
@if not exist %2\%3.mdb goto end
Start "%3" /b %2\%3.mdb
:end

[RuralGuy]

Thanks for the update and sharing the code. Did you want to mark this thread as Solved?