'Database in Use' from C# Application

[ChrisM]

Hi,
First of all, I'm not sure if this is the best group, but thought I'd start
general. If anyone can recommend a better place to ask this, suggestions are
welcome.
Anyway, on to the question:
I have a dotNet, C# application with about 10 users (Windows Forms, not ASP)
..
The basic setup is that each user has a copy of the program on their PC
which uses a local(ie on their PC) access database which contains the
queries and a set of linked tables that link to the actual datatables on a
single 'master' database which lives on the server.
So each user has a copy of the application and a copy of the 'queries'
database which all link to one 'data' database.
This is the connection string used to connect to the local 'queries'
database:
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=.\MyDb.mdb;Mode=Share Deny None;Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=1;Jet
OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet
OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet
OLEDB:Encrypt Database=False;Jet OLEDBon't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False";
It seems long, but I lifted it from somewhere else, and I'm not sure what
all the parameters actually do...
The problem is I am getting 'Database In Use' errors, not all the time, but
a few times a week. It even seems to happen if UserA is accessing one table
and UserB is accessing a totally unrelated one. Also, if I open a copy of
the 'queries' database on my PC and run a query then EVERYONE else gets a
Database In Use error if their copy of the application tries to access the
data. Please can anyone help me sort this out?
One other piece of information that may be relevant is the the problem is
worse since we upgraded our server operating system from NT4 to Microsoft
SBS2003.

Any suggestions or advise most gratefully received.

Thanks,

Chris.

 

[david epsom dot com dot au]

Your query database may have 'exclusive' links to the data.

The query database won't inherit the link properties from
the connection you quote, between your application and the
query database.

How did you create the links from the query database to
the data?

The way you have described it, the query database would
not maintain a persistent connection to the data.

So when you need to access the data, a connection is
created from the query database, then dropped. if those
connections are exclusive, then they will conflict if
some one else does something at the same time, or if
you hold one open.

Indecently, an exclusive link is more efficient than
a shared link, but holding the connection open is even
more efficient, and with only ten users, you can afford
to do that. Just choose or create a query through to
the data, and let your application hold the query open,
to hold the connection open. (You may want to create
a second connection from your application to do this,
so that your first connection is not fulfilling this
secondary function)

(david)

 

[ChrisM]

Hello David,

This sounds good...

I created the links as follows:
In the query database, Tables section,
<New...>
<Link Table>
Selected the database (eg \\server\Database\MyDATA.mdb)
Selected the tables that I wanted and pressed OK.

I don't fully understand your last paragraph about holding the connection
opening 'Just choose or create a query through to the data, and let your
application hold the query open...' Do you have some sample code or an
example that illustrates this?
I thought that the technique I was using was the recommended way of doing
this. Also, the number of users may increase in the future...
Should I create my linked tables using a different technique?

Thanks for your advice,

Chris.

 

[Brendan Reynolds]

It could be a permissions issue, Chris. The identity or identities under
which your code runs will need read, write and delete permission on the
folder that contains the data MDB, in order to read and write to the locking
(LDB) file and delete it when it is no longer in use.

You'll find more information in microsoft.public.dotnet.framework.adonet.
This type of question is asked there quite frequently.

 

[ChrisM]

Hi Brendan,
Thanks for your reply. Don't think it is a permissions thing though as the
problem is intermittant. Everyone's application will work fine for a couple
of days, then someone will get the error. When I have been able to check, it
has been when someone else has been running a slow(complex) query/report.
Hence I think it is only when two people attempt to read the database at the
same time. Most queries run in seconds or less and with so few users,
contention is quite rare. But it still happens...

Cheers,

Chris.

 

[Brendan Reynolds]

From your description, Chris, I don't think you can rule out a permissions
problem. I'm not in a position to test this at the moment, I'm going from
memory, so I certainly could be wrong. But I believe that the behaviour you
describe is consistent with what would happen if the identity under which
the code runs does not have write permission on the folder. As I remember
it, I believe that under those circumstances the first identity to connect
will get an exclusive connection, which will block further attempts to
connect until that connection is closed.

 

[ChrisM]

OK, I hear what you're saying. It seems to make perfect sense.
However, as far as I can tell, the necessary permissions DO exist.
From each PC I can create, amend and delete a file in the Server directory
that contains the database. Is that a sufficient test to confirm it isn't a
permissions issue?
Sorry if I'm asking dumb questions, in previous jobs I've had a 'systems'
guy to help me sort this kind of stuff out.

Cheers,

Chris.

 

[Brendan Reynolds]

Did you do this using the same identity under which your code runs?

 

[ChrisM]

Thought I'd replied to this, so apologies if it's me and this appears
twice...

Brendon,
As far as I know, the program runs under the user that is logged into the
computer.
There is nothing in the code to 'run as' anything/anyone else.

Cheers,

Chris.

 

[Brendan Reynolds]

My .NET experience is almost entirely with ASP.NET, Chris. In that
environment, code will often be executed using an identity other than that
of the logged on user. But I don't have enough experience of .NET Windows
Forms apps to know whether this is likely to be an issue in a Windows Forms
app or not. It's for reasons such as the above that I recommended the
ADO.NET newsgroup.

I agree that your test does seem to indicate that this is perhaps less
likely to be a permissions problem than I first thought. I'm just not sure
that we can entirely rule out the possibility. I think there's a strong
probability that you'll get a more definitive answer in the ADO.NET
newsgroup.

 

[david epsom dot com dot au]

Chris, unless you use code that you wrote yourself, you
are unlikely to get exclusive links. Using Access as you
describe, you will get shared links.

You won't be able to share the data unless you can write
your user name to the LDB file, which is a temp file in
the data folder, so you need to look at the permissions
on the data folder.

Normally when you have a lot of users you create a system
that allows them to share a connection to a database.

When you have fewer users, you let them make and drop
connections as required. So you could have 100 users, but
only 1 user connected at any point. And you only need
to pay for 10 connections to the database.

When you have only a couple of users, you just let them all
stay permanently connected.

You appear to be using a system where you are connecting
to the database to get data, then re-connecting to update
the data. This is the recommended system for a medium
number of users.

If you only have a small number of users, you can open
a recordset against your data, and leave it open. This
will remove the overhead of opening and closing the connection.

For an Access database with a small number of users, the
overhead of opening and closing the connection can cause
notable and unnecessary slowdowns.

(david)