Microsoft .Net / MS Access DB – Error – “Cannot open or write to the file”

Introduction

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.

 

Error Message

Image

AlreadyOpenedExclusively (croppedup)

 

Textual

************** 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 System.Data.OleDb.OleDbConnection.Open()
   at farmhand.frmReport.processOKClick()
   at farmhand.frmReport.button1_Click(Object sender, EventArgs e)

 

TroubleShooting

MS Access

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:

  1. Remote desktop to the server
  2. Launch Computer Management
  3. Access System Tools \ Shared Folders \ Open Files

Template

OpenFilesSteps

Opened Files

CM_20170301_0347PM

 

Exercise

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.

File Owner

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.

ChangedFileOwner (Cropped Up)

 

Effective Permissions

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.

EffectivePermissions_20170228_0538PM (cropped up)

 

Remediation

In our case the Access Database was corrupted and we needed to repair it.

Repair Access Database

Outline

Here are the steps to repair the Access Database:

  1. Using the OS File System, make a backup of the Access Database
  2. Launch MS Access
  3. Do not open the affected database
  4. 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

Images

Database Tools

DatabaseTools

 

Database to Compact From

DatabaseToCompactFrom

 

Compact Database Into

 

CompactDatabaseInto

 

 

Summary

In summary the areas to review are:

  1. The File System Permission
  2. Whether the Access Database is exclusively opened
  3. Whether the Access Database needs to be repaired

2 thoughts on “Microsoft .Net / MS Access DB – Error – “Cannot open or write to the file”

  1. Fantastic blog! Do you have any tips and hints for aspiring writers? I’m planning to start my own website soon but I’m a little lost on everything. Would you suggest starting with a free platform like WordPress or go for a paid option? There are so many options out there that I’m completely overwhelmed .. Any tips? Kudos!

    • Cardenas:

      I think you really just have to live life.

      Allow yourself experiences that are worth having and do not allow lapses that might get in the way of you writing about them later.

      Please let me know if is OK to email you directly.

      Daniel

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s