ADO wants exclusive access to MSAccess

[Siegfried Heintze]

I have a couple of perl programs that update an MS Access Database every
hour. The seem to coexist fine using the perl ODBC interface to INSERT,
UPDATE and SELECT.

I display the contents of the database (with limited editing capabilities)
using ADO.NET in an ASP.NET application (v1.1)

Here is my code:

string strConn = new string @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet
OLEDBatabase Password=;Data
Source=""C:\Inetpub\heintze\keyword-job-search\job-search.mdb;Password=;Jet
OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet
OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";

OleDbDataAdapter oCMD = new OleDbDataAdapter("SELECT
* FROM ...", strConn);
DataSet oDS = new DataSet();
oCMD.Fill(oDS, "MyList");

The above code only works if the perl programs are not running. Is there a
way to make this work? I always get the same error: 'could not lock file'.

Thanks,
Siegfried

 

[Cor Ligthert [MVP]]

Siegfried,

Did you already try it with a more simple connection string.

http://www.connectionstrings.com/

I hope this helps,

Cor

 

[W.G. Ryan - MVP]

Comments Inline:

I have a couple of perl programs that update an MS Access Database every
hour. The seem to coexist fine using the perl ODBC interface to INSERT,
UPDATE and SELECT.

I display the contents of the database (with limited editing capabilities)
using ADO.NET in an ASP.NET application (v1.1)

Here is my code:

string strConn = new string @"Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet
OLEDBatabase Password=;Data
Source=""C:\Inetpub\heintze\keyword-job-search\job-search.mdb;Password=;Jet
OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny
None;Jet
OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet
OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";

OleDbDataAdapter oCMD = new
OleDbDataAdapter("SELECT
* FROM ...", strConn);
DataSet oDS = new DataSet();
oCMD.Fill(oDS, "MyList");

The above code only works if the perl programs are not running. Is there a
way to make this work? I always get the same error: 'could not lock file'.

It may be that the limit has been exceeded -
http://support.microsoft.com/default.aspx?scid=kb;EN-US;815281

Also by any chance is the Access db sitting on a Novell File Server?
There's a known issue there as well
http://support.microsoft.com/default.aspx?scid=kb;en-us;305995

 

[Paul Clement]

¤ I have a couple of perl programs that update an MS Access Database every
¤ hour. The seem to coexist fine using the perl ODBC interface to INSERT,
¤ UPDATE and SELECT.
¤
¤ I display the contents of the database (with limited editing capabilities)
¤ using ADO.NET in an ASP.NET application (v1.1)
¤
¤ Here is my code:
¤
¤ string strConn = new string @"Jet OLEDB:Global Partial Bulk
¤ Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase Locking Mode=1;Jet
¤ OLEDBatabase Password=;Data
¤ Source=""C:\Inetpub\heintze\keyword-job-search\job-search.mdb;Password=;Jet
¤ OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
¤ Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
¤ database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet
¤ OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet
¤ OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica
¤ Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False";
¤
¤ OleDbDataAdapter oCMD = new OleDbDataAdapter("SELECT
¤ * FROM ...", strConn);
¤ DataSet oDS = new DataSet();
¤ oCMD.Fill(oDS, "MyList");
¤
¤ The above code only works if the perl programs are not running. Is there a
¤ way to make this work? I always get the same error: 'could not lock file'.

I would check to see if there is a corresponding .LDB file when the perl ODBC interface is used to
open the database. If not, then the perl ODBC app is opening the file for exclusive use and this
would need to be changed if you wanted to allow for concurrent multi-user access.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Siegfried Heintze]

Paul,
There is an ldb file when the perl program is accessing it.
Here is the connection string perl is using:

dbi:ODBC:driver={Microsoft Access Driver
(*.mdb)};dbq=c:\\Inetpub\\heintze\\keyword-job-search\\job-search.mdb

I'm also supplying a password and username to the function that opens the
connection.
Sieg

 

[Paul Clement]

¤ Paul,
¤ There is an ldb file when the perl program is accessing it.
¤ Here is the connection string perl is using:
¤
¤ dbi:ODBC:driver={Microsoft Access Driver
¤ (*.mdb)};dbq=c:\\Inetpub\\heintze\\keyword-job-search\\job-search.mdb
¤
¤ I'm also supplying a password and username to the function that opens the
¤ connection.
¤ Sieg
¤

This sounds like a permissions problem to me. Have you allowed full access to the folder where the
database is located for the ASPNET account, or depending upon the authentication level you are
using, the users of your web application?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Siegfried Heintze]

Here is some more information:

Presently I have one of the perl programs running collecting data and
storing it in my MSAccess database. It has been running since 11PM last
night. (I wrote this paragraph yesterday).

I just wrote another test perl program and pasted in the massive SELECT
statement from my C# aspx code into the perl test code. The perl test code
runs fine.

Can we infer from this test that there is nothing wrong with the perl code
and there is something in my C#/ASPX connection string that needs
adjustment?

I also tried converting my aspx from ole to odbc. This did not help. The
symptoms are the same: no problem as long as perl is not writing to the
database.

Paul: if it was directory permission problem, why does the problem occur
only when C#/ADO and Perl are trying to access the database simultaneously?
C#/ADO/OleDb and C#/ADO/ODBC work fine if there is no Perl/ODBC program
populating the database. I don't think it is a perl problem because the test
perl program I described above has no problem performing the same SELECT
statement that fails in ASPX/C#/ADO/ODBC.

Do you agree?

Thanks,
Siegfried

 

[Paul Clement]

¤ Here is some more information:
¤
¤ Presently I have one of the perl programs running collecting data and
¤ storing it in my MSAccess database. It has been running since 11PM last
¤ night. (I wrote this paragraph yesterday).
¤
¤ I just wrote another test perl program and pasted in the massive SELECT
¤ statement from my C# aspx code into the perl test code. The perl test code
¤ runs fine.
¤
¤ Can we infer from this test that there is nothing wrong with the perl code
¤ and there is something in my C#/ASPX connection string that needs
¤ adjustment?
¤

Well I'm not really sure what Perl does when it opens an Access database and updates it.
Theoretically it should just rely on the ODBC settings but I don't know that for certain. It's
possible it's placing an exclusive lock on the database or that it's creating the .LDB file under an
identity which prohibits the identity under which your ASP.NET app is running from accessing or
updating it.

¤ Paul: if it was directory permission problem, why does the problem occur
¤ only when C#/ADO and Perl are trying to access the database simultaneously?
¤ C#/ADO/OleDb and C#/ADO/ODBC work fine if there is no Perl/ODBC program
¤ populating the database. I don't think it is a perl problem because the test
¤ perl program I described above has no problem performing the same SELECT
¤ statement that fails in ASPX/C#/ADO/ODBC.
¤

Try using a very simple connection string to the Access database from your ASP.NET app. Most of
those arguments do not need to be specified.

http://www.carlprothman.net/Default.aspx?tabid=87#OLEDBProviderForMicrosoftJet


Paul
~~~~
Microsoft MVP (Visual Basic)