Intranet/Desktop using same backend DB

[Dave57]

This is the scenario:


I have a maintenance office that has been using an Access 2003 system to track work orders. The backend database resides on the network and we have installed the frontend on the users’ PCs.

I have added a .aspx form to the company’s intranet site so they can submit work orders there. I moved the Access backend DB from its current network location to the web app server and was successful at connecting the web form to it. I was also successful at linking the frontend DB to the backend DB in this new location. However, when someone is in the thick client app (the maintenance office) the web form crashes and says :
Server Error in '/' Application.


Could not use ''; file already in use.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Could not use ''; file already in use.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Has anyone solved this kind of an issue?

[ItsMe]

The "file already in use." thing is curious. Do you have a limit of connections allowed on IIS or the ODBC. I was first thinking that the ODBC connection was not properly defined within the Desktop App after the migration.

[Dave57]

I am not usin ODBC to link to the tables, I'm using the link table manager as I have permissions setup to get to other server. Where in IIS would the limit of connections be found?

[ItsMe]

I do not believe Access 2003 supports aspx. You can create Active Server pages in the desktop app. These use .asp extension. The Active Server Page is an Object within the desktop app. The asp object is created by Access 2003 and stored on the IIS. There is an ODBC connection to IIS in order for it to work from the desktop app. Access Active Server Pages have been deprecated. I believe they used the 1.0 or 1.X .net framework. It has been many years since I have used Active Server Pages.

As for multiple connections, I believe the permissions are under security for the Web Server within IIS, somewhere around the allow anonymous thing. As for ODBC, I mention it because I believe you need it in order to connect to the IIS from the desktop app. Once again... many moons ago.

So I am wondering where your web app is failing. Are you trying to use the Access Run Time to run your web app? Why not use a browser window?

[Dave57]

I have added a form to an existing intranet site and am trying to connect it to an existing Access backend database, when I open the browser and put in the URL for my form, it works pefectly. Its only when there is some one in the desktop Access app that the web form dies.

[ItsMe]

I have added a form to an existing intranet site and am trying to connect it to an existing Access backend database, when I open the browser and put in the URL for my form, it works pefectly. Its only when there is some one in the desktop Access app that the web form dies.

OK, I see the picture now. Not sure I know why this is happening, but I will keep it in mind.

[Dave57]

OK, thanks

[ItsMe]

First, I am not an expert in web apps. I have been dreading joining the ranks and yelling out, "Look at me, I'm in the cloud!" But, it is inevitable...

Since you state the web page works on a machine and then, on the same machine, the page fails if another user has Access open.....

I am going to guess there is a record lock issue that your code in the web page does not have error trapping for. Perhaps an edit or update command. The only other thing I can imagine is that the user on the other machine is opening Access in Exclusive mode somehow, maybe via Client Options within Access.

[Dave57]

I did notice that when the web form connects to the backend MS Access database a lock file (.ldb) is NOT created. However, I can fill out the form and submit it and the data is stored in the database, as long as no one gets in the desktop MS Access frontend. I also noticed that when a user logs in to the desktop system, a lock file (.ldb) is created if there is a user fills in the form and submits it, it dies. There is something about the lock file (.ldb) the the web form does not like.

[ItsMe]

If I recall correctly the ldb file will be created for one of two reasons. A user opens an mdb file directly, or a table is accessed via a link or connection. So, if you open an mdb file locally on your machine, another Access file that acts as the backend will not create an ldb until you access the BE tables. I would suspect the same for your web form. The web form will not (cause the BE file to) create an ldb file until it accesses one of the tables in the backend File. When the web form is done accessing the table, the connection is closed, the ldb file should disappear.


Your web form should be dependent on the Jet 4.0 database engine. This is apparent because of the error message you are getting and well, I don't know of another way to access an MDB file. I would expect the engine to work just fine with the ldb file.

So your web app is able to manipulate data in the tables or even just view real time data without creating an ldb file?

EDIT: What IDE and language are you using to write your code and create the connection?

[Dave57]

You're right when I execute the INSERT INTO sql statement the ldb file is present and when the connection is closed it disappears. I'm beginning to think I will use some kind of a batch file to dump data to the Access DB every night from another independent DB.

[Dave57]

My code behind is VB.net in VS2008

[ItsMe]

This is the nature of web forms. You do not want your web app sucking up bandwidth by constantly staying connected to the BE.

If you are interested in testing the locked record theory, you could create a temp Access mdb file. In this file link one table to the BE file that is not associated with your web form. Open the new mdb file and open the link object. After you observe an ldb file being created on the BE, test your web app.

If you wish, you can post your code from the web app here. Select some code that you feel is relevant to your web form fetching and updating data. I cannot guarantee I will be able to help. But I will take a look at it. I will be signing off soon but will be checking in periodically.