UI accdb gets corrupted on access 2010

0
votes

I have migrated an Access application (for a client of mine) from Access 2003 to Access 2010. This means:

  • The application was composed of a set of 4 "UI" databases accessing 2 "data" databases.
  • All 6 databases were in Access 2002/2003 .mdb format
  • All 6 databases were and are still located on a CIFS network share.
  • We migrated all 4 "UI" databases to Access 2010 .accdb format.
  • We left both "data" databases in their original format.
  • The data tables are linked through standard Access "linked tables" mechanism. (No ODBC or such)

The problem my client is facing is that the UI database becomes corrupted on a regular basis, the error message being something like "The input expression contains a function name that Microsoft Access cannot find". It is preceded by a proposal to repair the file, which has been damaged.

I am suspecting some user action to interrupt the program due to poor responses time of Access 2010, but if it turns out not to be the case, the question is: Is there a known problem with the accdb file format located on a network share?

1
ms-accessms-access-2010

1 Answers

1
votes

I found most of my issues occur when everyone uses the same "UI"/front end databases. I modified a vbs script that makes a copy of the front end database to a local drive and use that instead of the original.

Wasn't able to find the link to the original script. Here is my version of the script.

Option Explicit

Dim objShell
Dim objFSO
Dim strHomeFolder
Dim strFullPath
Dim strDestinationFolder
Dim strDestinationFullPath
Dim returnValue

Set objShell = CreateObject("WScript.Shell")
strHomeFolder = objShell.ExpandEnvironmentStrings("%APPDATA%")

strFullPath = "\\networkPathToOriginalDatabase"
strDestinationFolder = strHomeFolder & "\myFolderName"
strDestinationFullPath = strDestinationFolder & "\myDataBaseName"

Set objFSO = CreateObject("Scripting.FileSystemObject")
returnValue = 0

If Not objFSO.FolderExists(strDestinationFolder) Then
    returnValue = objFSO.CreateFolder(strDestinationFolder)
End If

returnValue = objFSO.CopyFile(strFullPath, strDestinationFullPath, True)

' open the front end
objShell.Run """" & strDestinationFullPath & """", 1, false

' cleanup
Set objShell = Nothing
Set objFSO = Nothing

Here are also some general Access practices (there is a section on corruption)

http://allenbrowne.com/tips.html