Frequent Database Corruption - please help

[scotiwis]

Hi,
Would very much appreciate people's input here as I am struggling to solve this and it is causing our users a lot of headaches. I am at my wit's end.
I have an ACCDB 2016 Access database hosted on a single 2012 R2 server, split SQL back end, MS Office 2016. Each user connects to the server via remote desktop and a login script copies the ACCDB from a central location to their local documents which is where they access it.


The users are randomly getting corruption on the database. The errors and behaviour include:

• Error messages about can't find macro, can't find VBA code when clicking on something (as if the macro and VBA coding have vanished underneath)
• Error message Cannot open database '', expression may not result in name of a macro or event procedure, detected database in an inconsistent state, cannot be opened because VBA project cannot be read, unrecognized database format etc
• User using the database then leaves it idle for a while, comes back, can't click anything (one or more of the errors above), then crashes
• User using the database and the above happens as they are actively using it (although the idle one above is more common than this one)

I have tried the following without success:

• Create new blank database, import everything into it (this seemed to work well for a while and then the corruption started again)
• Decompile / recompile / compact/repair
• Removing references, adding references
• Creating ACCDE
• Repairing Office
• Ensuring Windows and Office up to date via Windows Update

More info:

• I have been developing the master ACCDB for the last 10 months under my adminatrative remote desktop on the server, I have never once encountered the corruption the users are getting
• The users each have a restricted remote desktop on the server, locked down by GPO (can't access internet, can only open office docs, etc)
• I have a test login with the same restrictions as the users that I have used the database in for hours and days at a time without encountering the error (ie it's very hard to replicate, random)
• The users will get the corruption on average two or three times per day - no pattern to when, which screen/report, which user, sometimes not at all - all "random"
• I managed to copy one of the user's database when they got the error and the VBA code in the database was inaccessible (in fact it looked like it just wasn't there)

Thanks

[aytee111]

That is really bad, they must be climbing the walls! One thing you can try, give them an accde version, that will have the code etc removed, see if that helps.

[scotiwis]

Yeah, tried that. Have tried with them using ACCDE and ACCDB, same results

[aytee111]

Leave the front-end on the network?

Does 2016 have a run-time version?

[Bulzie]

I have experienced similar corruption. Find out when it started happening and try to get a backup copy of database before then. Test and see if it runs or has same issues. Has it been working find for awhile and all of a sudden started with the errors? Maybe save as earlier version of MS Access and see if it will run? Is the be database SQL or Access?

[scotiwis]

Managed to trace it to group policy File Copy (User Config - Preferences - Files). This is used to copy the accdb from central location to user's documents folder at remote desktop logon. The file seems to copy fine when they log on (replaces the one that's there). They are definitely then using the local copy of the file (checked this) and then at some point the corruption occurs while they're using it. I removed the File Copy, copied the central db to their local folder manually, and the corruption doesn't occur. Anybody know why this would be? Is there an alternative to GPO file copy I could try.
Thanks

[Bulzie]

I do that copy when they click the icon to open the program, have a small vb exe that basically checks for c:\xxx folder, creates it if not there, then copies database to that location and launches the program in MS Access. So they are always in the latest copy.

[scotiwis]

Hey Bulzie, that sounds interesting, so it's a little standalone database that launches for each user locally and that db checks folder / copies / opens? Could you paste the code onto here that you use and I'll try and replicate. Many thanks

[Bulzie]

It's a VB exe, not Access.


Private Sub Form_Load()
On Error GoTo ErrTrap

AppActivate "Therapy Scheduling System" '** checks to see if the app is open already **

Form1.Hide
MsgBox "The Therapy Scheduling System is already open.", vbSystemModal
Unload Form1
Set Form1 = Nothing

End

CopyAndLoad:
If Dir("c:\PTS", vbDirectory) = "" Then MkDir ("c:\PTS") '** checks for the directory on PC, creates it if not there and copies the FE database to that location **
FileCopy "\\prog\apps\PTScheduling\PTScheduler.accdb", "c:\PTS\PTScheduler.accdb"

If Dir("C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then '** finds MS Access and opens the database **

Shell "C:\Program Files (x86)\Microsoft Office\OFFICE12\MSACCESS.EXE c:\PTS\PTScheduler.accdb", vbNormalFocus

Else

If Dir("C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE", vbDirectory) <> "" Then

Shell "C:\Program Files\Microsoft Office\OFFICE12\MSACCESS.EXE c:\PTS\PTScheduler.accdb", vbNormalFocus

Else

If Dir("C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then

Shell "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE c:\PTS\PTScheduler.accdb", vbNormalFocus

Else

If Dir("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", vbDirectory) <> "" Then

Shell "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE c:\PTS\PTScheduler.accdb", vbNormalFocus

Else

If Dir("E:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe", vbDirectory) <> "" Then

Shell "E:\Program Files (x86)\Microsoft Office\Office12\msaccess.exe c:\PTS\PTScheduler.accdb", vbNormalFocus

Else

MsgBox "Could Not Find Microsoft Access Progrm. Please contact IS Programming Group.", vbSystemModal

End If

End If

End If

End If

End If
Unload Form1
Set Form1 = Nothing
End
Exit Sub
Resume

ErrTrap:
Select Case Err.Number

Case 5

GoTo CopyAndLoad

Case 70

MsgBox "Could Not Copy Current Version", vbSystemModal

Unload Form1

Set Form1 = Nothing

End

End Select
End Sub

[scotiwis]

ok I've never created a VB exe, don't have visual studio installed. Would just running a batch file from the shortcut achieve the same thing? Any advantages to the VB over a batch file?
Thanks

[Bulzie]

Whatever works, try it and see. The basic parts are
1. Checks to see if program is already open
2. Checks to see if local folder created, if not it creates it
3. Copies database to the local folder
4. Finds the location of whatever version of MS Access you have and opens the database

Some of those steps can be optional or different depending on your situation.

[scotiwis]

ok, I've got the batch file working, where should the batch file reside - should it be on the network share or local profile. Network share would be easier to manage, but what happens if say two people launch their db's at the same time, so two people running the same batch file at the same time. Any problem with that?

[isladogs]

Cross posted here, also with answers
https://www.access-programmers.co.uk...=1#post1553211