Excel Database File Is Locked – CODESOFT Problem Article

Summary

For smaller labeling systems, Excel is often used as a database due to its simplicity.  Under certain conditions, however, Windows can lock the file preventing other users from accessing it. A file in a ‘locked’ state can stop CODESOFT from accessing it.

Symptoms

  • The CODESOFT label design is configured to connect to Excel
  • User receives and error when opening the label file

Explanation

This is a file locking situation. If the label file is open and the OLEDB connection is set with write or modify access it will open the associated Excel document in an exclusive mode – meaning no one else could open the file for update. Since Excel is not a true database, Windows needs to protect the file from corruption by only allowing one user at a time to alter it. Thus, Windows locks the file for editing. If the file is already opened by another program (Excel), the label file will fail during opening because it cannot obtain an exclusive lock on the file.

Solution

A couple of potential solutions:

  1. Modify the OLEDB Settings to ‘Read-Only’ Mode – this should prevent Windows from locking out CODESOFT (other applications may still be locked out).
  2. Connect CODESOFT to a Fully Functional Database Server (e.g. SQL) – using a fully functional database server avoids the locking of files since the database server can manage multiple connections.
Modify the OLEDB Settings to Read-Only Mode

In the label design, set the database connector to read-only mode.

  1. Data source/Database/Create Edit Query
  2. Select OLEDB from the “New” button
  3. Select the connection that is associated with the Excel document and click Edit
  4. Select Advanced tab
  5. In Access permissions, clear all the check mark except for the one for ReadClick OK,

Connect CODESOFT to a Fully Functional Database Server (e.g. SQL)

This is a better long term solution but does involve moving the data to a SQL database (SQL Express is OK for this). If the user is familiar with Excel, it can be configured to allow the user to work within Excel to save data into the SQL database instead of a typical Excel file.

Additional Comments

Enter additional comments here

Updated on September 20, 2017

Was this article helpful?

Need Support?
Can’t find the answer you’re looking for? Don’t worry we’re here to help!
Contact Support