1

I have a C# application that interacts with an Access database.
When I execute an OleDbCommand object to ALTER a table and ADD a new column, I receive an error:

OleDbException: The database engine could not lock table because it is already in use by another person or process.

While I can manipulate my data successfully. However when I execute the query directly in the MS Access, it works correctly.

Note: my database is closed and no person or process is using it.

How can I resolve this?

Michael Petrotta
  • 59,888
  • 27
  • 145
  • 179
Hamid62
  • 183
  • 4
  • 15

1 Answers1

0

Try this out

Use following connection string

db.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\<Your database name>;Persist Security Info=False;")

along with following additional property

db.Properties("Jet OLEDB:Connection Control") = 1

In short, you need to open the database in exclusive mode to make an alter table query. or you can also set this property in the following manner in c#

CN.Mode =adModeShareExclusive

I have not tested the code though but it should work for you.

Murtuza Kabul
  • 6,438
  • 6
  • 27
  • 34
  • What is the `db` and `CN`? My connection string is : `"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=;Persist Security Info=False;"` – Hamid62 Oct 27 '12 at 06:49
  • The Jet connection is for earlier versions of Access and will not work with 2007 onward. The Ace connection is backwardly compatible. – Fionnuala Oct 27 '12 at 09:42
  • You do not need exclusive mode to alter a table, you just need that the table is not in use when working in MS Access, and I am fairly sure the same is true in C#. I suspect that the table may be opened elsewhere in code. – Fionnuala Oct 27 '12 at 09:45
  • Thank you `Remou`. Yes I had open the connection to select the Schema of a table before I alter it. I closed it after select and opened another connection again to alter the table and it WORKS. – Hamid62 Oct 28 '12 at 10:22