Jet OLEDB connection from WebService fails if another app has DB open

[John]

Hello:

I have a web service that uses a ACCESS 2000 db and it
works fine if no other app has the DB open. However, if
another app on the server is using the DB, I get an
exception:

Could not use ''; file already in use.. (verbatim)

I am using the same connect string in both the Web Service
and the app :

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb"

Any help would be greatly appreciated.

John

[Ken Schaefer]

Are they both running under the same user context?
In Access, do you have default open mode set to "shared" not "exclusive"?,
and "use record level locking"?

Otherwise, you may be runing into the inherent limits of Access. Have you
considered using MSDE instead?

Cheers
Ken

"John" <anonymous@discussions.microsoft.com> wrote in message
news:070101c3bf83$1ef6d620$a101280a@phx.gbl...
: Hello:
:
: I have a web service that uses a ACCESS 2000 db and it
: works fine if no other app has the DB open. However, if
: another app on the server is using the DB, I get an
: exception:
:
: Could not use ''; file already in use.. (verbatim)
:
: I am using the same connect string in both the Web Service
: and the app :
:
: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb"
:
: Any help would be greatly appreciated.
:
: John

[Bob Barrows]

John wrote:

> Hello:
>
> I have a web service that uses a ACCESS 2000 db and it
> works fine if no other app has the DB open. However, if
> another app on the server is using the DB, I get an
> exception:
>
> Could not use ''; file already in use.. (verbatim)
>
> I am using the same connect string in both the Web Service
> and the app :
>
> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb"

Is this really the connection string? Did you simply not show us the name of
the folder containing the database? If no, then you should provide the full
file-system path to the database. Otherwise it will expect to find it in the
root, not a good place to store a database.

>
> Any help would be greatly appreciated.
>
> John

Do the IUSR and IWAM accounts have NTFS Change permissions on the folder
containing the database? This is required to allow multi-user activity.


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

>-----Original Message-----
>John wrote:

>> Hello:
>>
>> I have a web service that uses a ACCESS 2000 db and it
>> works fine if no other app has the DB open. However, if
>> another app on the server is using the DB, I get an
>> exception:
>>
>> Could not use ''; file already in use.. (verbatim)
>>
>> I am using the same connect string in both the Web

Service

>> and the app :
>>
>> "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb"

>
>Is this really the connection string? Did you simply not

show us the name of

>the folder containing the database? If no, then you

should provide the full

>file-system path to the database. Otherwise it will

expect to find it in the

>root, not a good place to store a database.

I have the full path in the connect string, I just left it
out for brevity.

>

>>
>> Any help would be greatly appreciated.
>>
>> John

>
>
>Do the IUSR and IWAM accounts have NTFS Change

permissions on the folder

>containing the database? This is required to allow multi-

user activity.

>

How can I set this?

>
>Bob Barrows
>--
>Microsoft MVP - ASP/ASP.NET
>Please reply to the newsgroup. This email account is my

spam trap so I

>don't check it very often. If you must reply off-line,

then remove the

>"NO SPAM"
>
>
>.
>

[Bob Barrows]

[email]anonymous@discussions.microsoft.com[/email] wrote:

>> Do the IUSR and IWAM accounts have NTFS Change permissions on the
>> folder containing the database? This is required to allow multi-
>> user activity.
>>

>
> How can I set this?
>

Really? Is it the "NTFS" that's throwing you off? Just use Windows NT
Explorer to navigate to the folder. Right-click the folder name, choose
properties from the popup menu, click into the Security tab, add the IUSR
and IWAM accounts (in some OS's they are referred to as the Internet Guest
accounts), and grant them sufficient permissions to read and write to the
folder.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[John]

Hi Ken:

Both options are set in Access, but I think the Web
service is running under IIS and the other app is just a
desktop app that uses the same database.

I am thinking of trying MSDE if I cannot find a workaround.

If I stop the Web Service in the debugger after it has
connected, and then start the desktop app and let it
connect , both work fine, I think this is because the Web
Service is pooling the original connection.

>-----Original Message-----
>Are they both running under the same user context?
>In Access, do you have default open mode set to "shared"

not "exclusive"?,

>and "use record level locking"?
>
>Otherwise, you may be runing into the inherent limits of

Access. Have you

>considered using MSDE instead?
>
>Cheers
>Ken
>
>"John" <anonymous@discussions.microsoft.com> wrote in

message

>news:070101c3bf83$1ef6d620$a101280a@phx.gbl...
>: Hello:
>:
>: I have a web service that uses a ACCESS 2000 db and it
>: works fine if no other app has the DB open. However, if
>: another app on the server is using the DB, I get an
>: exception:
>:
>: Could not use ''; file already in use.. (verbatim)
>:
>: I am using the same connect string in both the Web

Service

>: and the app :
>:
>: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=myDB.mdb"
>:
>: Any help would be greatly appreciated.
>:
>: John
>
>
>.
>

[Bob Barrows]

John wrote:

> Hi Ken:
>
> Both options are set in Access, but I think the Web
> service is running under IIS and the other app is just a
> desktop app that uses the same database.

All users of the database require Change permissions to the folder
containing the database in order for multiple-user activity to take place.

>
> I am thinking of trying MSDE if I cannot find a workaround.

Can't hurt.

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

OK, I did that, but still the problem persists.
Do I need to restart the computer?

>-----Original Message-----
>anonymous@discussions.microsoft.com wrote:

>>> Do the IUSR and IWAM accounts have NTFS Change

permissions on the

>>> folder containing the database? This is required to

allow multi-

>>> user activity.
>>>

>>
>> How can I set this?
>>

>
>Really? Is it the "NTFS" that's throwing you off? Just

use Windows NT

>Explorer to navigate to the folder. Right-click the

folder name, choose

>properties from the popup menu, click into the Security

tab, add the IUSR

>and IWAM accounts (in some OS's they are referred to as

the Internet Guest

>accounts), and grant them sufficient permissions to read

and write to the

>folder.
>
>Bob Barrows
>
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[john]

I added the permissions for IUSER and IWAM, but same thing.

I don't think MSDE will work for me since it has a 2 gig
size limit, whereas Jet can use multiple 2 gig mdb files.

>-----Original Message-----
>John wrote:

>> Hi Ken:
>>
>> Both options are set in Access, but I think the Web
>> service is running under IIS and the other app is just a
>> desktop app that uses the same database.

>
>All users of the database require Change permissions to

the folder

>containing the database in order for multiple-user

activity to take place.

>

>>
>> I am thinking of trying MSDE if I cannot find a

workaround.

>
>Can't hurt.
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Bob Barrows]

[email]anonymous@discussions.microsoft.com[/email] wrote:

> OK, I did that, but still the problem persists.
> Do I need to restart the computer?
>

I don't thinks so, but it can't hurt. Beyond this, I am stumped, having
never used web services. You might want to try on one of the dotnet groups
....

Wait. In your first message you said this was the error:
Could not use ''; file already in use.. (verbatim)

Did it really not put in the name of the file? And does it really say
"file"?
Check out this article: [url]http://www.aspfaq.com/show.asp?id=2009[/url]


Bob Barrpws

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows]

john wrote:

> I added the permissions for IUSER and IWAM, but same thing.
>
> I don't think MSDE will work for me since it has a 2 gig
> size limit, whereas Jet can use multiple 2 gig mdb files.

Seriously? You're expecting that much data? I know Access CAN handle that
much data, but, the question is, "will it?". Especially in a multi-user
environment. You need a real server-based rdbms, such as SQL Server or
Oracle.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[john]

I think what is happening is that the desktop app is
opening the DB in some exclusive mode if it can. What I
did was stop the web service in the debugger once it had a
connection, then started the desktop app and let it
connect. Then i let the web service go and it would work
fine (with the desktop app running) call after call UNTIL
I shutdown the desktop app and restarted it while the web
service was idle.

So I interpret this as the desktop taking an exclusive
hold on the db if it can, otherwise it opens a shared
connection with the existing web service connection.

I can run multiple connections from various desktop apps,
so I guess the exclusivity only shuts out the web service
or maybe other IIS threads.

Is there any property I can set in the connect string so
it will share the connection or something?

>-----Original Message-----
>anonymous@discussions.microsoft.com wrote:

>> OK, I did that, but still the problem persists.
>> Do I need to restart the computer?
>>

>I don't thinks so, but it can't hurt. Beyond this, I am

stumped, having

>never used web services. You might want to try on one of

the dotnet groups

>....
>
>Wait. In your first message you said this was the error:
>Could not use ''; file already in use.. (verbatim)
>
>Did it really not put in the name of the file? And does

it really say

>"file"?

Yes, that is the EXACT sentence.

>Check out this article: [url]http://www.aspfaq.com/show.asp?[/url]

id=2009

>
>
>Bob Barrpws
>
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[john]

Actually, this is a large (20+ processes/services) server
based system for tracking military email, and I agree that
we should use a better db, but I guess the prices are
pretty high for SQL server or Oracle.

>-----Original Message-----
>john wrote:

>> I added the permissions for IUSER and IWAM, but same

thing.

>>
>> I don't think MSDE will work for me since it has a 2 gig
>> size limit, whereas Jet can use multiple 2 gig mdb

files.

>
>Seriously? You're expecting that much data? I know Access

CAN handle that

>much data, but, the question is, "will it?". Especially

in a multi-user

>environment. You need a real server-based rdbms, such as

SQL Server or

>Oracle.
>
>Bob Barrows
>
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Bob Barrows]

john wrote:

> I think what is happening is that the desktop app is
> opening the DB in some exclusive mode if it can. What I
> did was stop the web service in the debugger once it had a
> connection, then started the desktop app and let it
> connect. Then i let the web service go and it would work
> fine (with the desktop app running) call after call UNTIL
> I shutdown the desktop app and restarted it while the web
> service was idle.
>
> So I interpret this as the desktop taking an exclusive
> hold on the db if it can, otherwise it opens a shared
> connection with the existing web service connection.
>
> I can run multiple connections from various desktop apps,
> so I guess the exclusivity only shuts out the web service
> or maybe other IIS threads.
>
> Is there any property I can set in the connect string so
> it will share the connection or something?
>

Yes, but it is on by default. And it will do no good in the web service if
the desktop app has opened the db exclusively.

[url]http://msdn.microsoft.com/library/en-us/ado270/htm/mdapro04_4.asp[/url]

Take a look at this as well:
[url]http://msdn.microsoft.com/library/en-us/ado270/htm/pg_ado_providers_5.asp[/url]


Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[john]

Bob:

Thanks for your help. I actually got it to work by setting
the permissions on the folder like you suggested. I guess
I didn't do it right the first time.

Thanks again,

John

>-----Original Message-----
>john wrote:

>> I think what is happening is that the desktop app is
>> opening the DB in some exclusive mode if it can. What I
>> did was stop the web service in the debugger once it

had a

>> connection, then started the desktop app and let it
>> connect. Then i let the web service go and it would work
>> fine (with the desktop app running) call after call

UNTIL

>> I shutdown the desktop app and restarted it while the

web

>> service was idle.
>>
>> So I interpret this as the desktop taking an exclusive
>> hold on the db if it can, otherwise it opens a shared
>> connection with the existing web service connection.
>>
>> I can run multiple connections from various desktop

apps,

>> so I guess the exclusivity only shuts out the web

service

>> or maybe other IIS threads.
>>
>> Is there any property I can set in the connect string so
>> it will share the connection or something?
>>

>Yes, but it is on by default. And it will do no good in

the web service if

>the desktop app has opened the db exclusively.
>
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/mdapro04_4.asp

>
>Take a look at this as well:
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/pg_ado_providers_5.asp

>
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[john]

Bob:

So I had to set Read/Write Access on the directory
containing the DB files, but does this mean that some
internet user can now access the directory and read/write
without going through my web service?

John

>-----Original Message-----
>john wrote:

>> I think what is happening is that the desktop app is
>> opening the DB in some exclusive mode if it can. What I
>> did was stop the web service in the debugger once it

had a

>> connection, then started the desktop app and let it
>> connect. Then i let the web service go and it would work
>> fine (with the desktop app running) call after call

UNTIL

>> I shutdown the desktop app and restarted it while the

web

>> service was idle.
>>
>> So I interpret this as the desktop taking an exclusive
>> hold on the db if it can, otherwise it opens a shared
>> connection with the existing web service connection.
>>
>> I can run multiple connections from various desktop

apps,

>> so I guess the exclusivity only shuts out the web

service

>> or maybe other IIS threads.
>>
>> Is there any property I can set in the connect string so
>> it will share the connection or something?
>>

>Yes, but it is on by default. And it will do no good in

the web service if

>the desktop app has opened the db exclusively.
>
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/mdapro04_4.asp

>
>Take a look at this as well:
>[url]http://msdn.microsoft.com/library/en-[/url]

us/ado270/htm/pg_ado_providers_5.asp

>
>
>Bob Barrows
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Bob Barrows]

john wrote:

> Bob:
>
> So I had to set Read/Write Access on the directory
> containing the DB files, but does this mean that some
> internet user can now access the directory and read/write
> without going through my web service?
>

Not if this folder is not in the website structure. As long as it is outside
of inetpub, no internet user should have access to it outside of your
application.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Ken Schaefer]

....and you are running it off Access? which is designed as a single user
desktop database? (single threaded, no transaction logs, etc etc)

Wow - you're brave! I'd admire your courage.

mySQL will do the job better (and it's free, as in beer).
FoxPro is a little "dated" (i.e. not viewed as glamourously as SQL Server or
Oracle), but it's also cheaper.

Cheers
Ken


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"john" <anonymous@discussions.microsoft.com> wrote in message
news:02d901c3c02a$06f53b00$a401280a@phx.gbl...
: Actually, this is a large (20+ processes/services) server
: based system for tracking military email, and I agree that
: we should use a better db, but I guess the prices are
: pretty high for SQL server or Oracle.
:
:
:
:
:
: >-----Original Message-----
: >john wrote:
: >> I added the permissions for IUSER and IWAM, but same
: thing.
: >>
: >> I don't think MSDE will work for me since it has a 2 gig
: >> size limit, whereas Jet can use multiple 2 gig mdb
: files.
: >
: >Seriously? You're expecting that much data? I know Access
: CAN handle that
: >much data, but, the question is, "will it?". Especially
: in a multi-user
: >environment. You need a real server-based rdbms, such as
: SQL Server or
: >Oracle.
: >
: >Bob Barrows
: >
: >--
: >Microsoft MVP -- ASP/ASP.NET
: >Please reply to the newsgroup. The email account listed
: in my From
: >header is my spam trap, so I don't check it very often.
: You will get a
: >quicker response by posting to the newsgroup.
: >
: >
: >.
: >

[Ken Schaefer]

Then you have two different users accessing the database - that is going to
cause problems I think. If you can get both to run under the same account
you will have less issues (not saying they'll all go away though).

Cheers
Ken


"John" <anonymous@discussions.microsoft.com> wrote in message
news:122c901c3c019$60fc78e0$a601280a@phx.gbl...
: Hi Ken:
:
: Both options are set in Access, but I think the Web
: service is running under IIS and the other app is just a
: desktop app that uses the same database.
:
: I am thinking of trying MSDE if I cannot find a workaround.
:
: If I stop the Web Service in the debugger after it has
: connected, and then start the desktop app and let it
: connect , both work fine, I think this is because the Web
: Service is pooling the original connection.