Recently had to help troubleshoot an issue that dealt with an in house application.
The application is written in Microsoft .Net and it interacts with two databases.
An upgraded database that started out in MS Access, but is now in SQL Server.
And, a portion that still resides in MS Access.
************** Exception Text **************
System.Data.OleDb.OleDbException: The Microsoft Access database engine cannot open or write to the file ‘\\AppServer\Farm\Family.mdb’. It is already opened exclusively by another user, or you need permission to view and write its data.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at farmhand.frmReport.button1_Click(Object sender, EventArgs e)
MS Access Database Opened in Exclusive Mode
Spent quite a bit of time tracking down the Windows Share and File Usage using computer management.
On a MS Windows 2012 Server, the steps to follow is:
- Remote desktop to the server
- Launch Computer Management
- Access System Tools \ Shared Folders \ Open Files
For this exercise, I consistently misread the problem and thought the Access DB was opened exclusively.
OS File System Security
Next in line was to review Share permissions, file ownership, and file privileges.
Again, the OS, Windows 2012, handles this very gracefully.
Review the File Owner and make sure that it is valid.
Validation is based on the fact that the Account or Group exists in the local SAM Account or AD.
Here we chose one of the users that was failing and asked the OS for the “Permission Set” that is currently effective for that user.
In our case the Access Database was corrupted and we needed to repair it.
Repair Access Database
Here are the steps to repair the Access Database:
- Using the OS File System, make a backup of the Access Database
- Launch MS Access
- Do not open the affected database
- Access the “Database Tools” Tab
- Click the “Compact and Repair Database”
- In the “Database to Compact From” File Open Dialog, choose the file to repair
- In the “Compact Database Into”, enter the name to give the repaired file
Database to Compact From
Compact Database Into
In summary the areas to review are:
- The File System Permission
- Whether the Access Database is exclusively opened
- Whether the Access Database needs to be repaired