Accessing database with read only permissions?

  • Thread starter Thread starter Jeff Shanholtz
  • Start date Start date
J

Jeff Shanholtz

I have an access database on a network with more than one client accessing
it. Most of the time the clients will only be reading from the database, so
I want to program it to use read-only mode so that if a second client needs
to write to the database, the first client won't unnecessarily prevent this
simply by having an open connection.

I've been searching with google and in this newsgroup but I haven't come up
with anything (maybe I'm not effectively choosing my search terms?). I
can't believe it's as hard as it seems to find some discussion of this.
Surely this is possible and surely it is commonly done, right? I'm guessing
it is something I put in my connection string.

I am using C#, .net, and OleDb classes (OleDbDataAdapter, OleDbCommand,
OleDbConnection).

I would appreciate any advice you all can give.
 
If you really want control user access on the database level, for Jet DB
(Access DB) you have to implement user level security in Access (serach MS
site for "Access database user level security" or Access Help.

But if you only want to avoid "open connection", and your users only deal
the data through you .NET app, then you may not need to bother the fairly
complicated user level security thing in Access. The point is you should
avoid keeping connection open unless you have some special reasons.

By default, ADO.NET deals with database in a discoonected fashion. The only
time your app keep an open connection is that you use DataReader and did not
close it while user viewing data on the screen, which you should not do
generally. What you should do for data viewing is to open connection, get
data from db in single or a batch of queries into , say, a dataset, then
close connection. how user can view the data as long as he wants. The same
time, other user may update the database. If the first user wants to do
update at some point (the underline data may have been updated), ADO.NET
would raise "Concurrent conflict error" and you would handle that, based on
your situation.
 
First, user level security isn't what I'm after. Second, are you suggesting
that there is no way to choose to make a read-only connection to the
database? It appears that the first connection is read-write automatically,
and any subsequent connections from other clients are read-only.

What I want to avoid is a message to the user who has the 2nd connection
along the lines of "someone else currently has a connection to the
database, so wait a moment and retry" when that "someone else" isn't even
writing to the database.

As far as I can tell, there is a tradeoff between keeping connections open
and not. If I keep them open, I have this access problem with multiple
clients (though the problem can still occur even when connections don't
stay open for long), but if I keep them closed, changes that a user makes
won't be available to himself for a short time (seems to be a few seconds)
due to the fact that jet doesn't immediately commit the changes to the
database and AFAIK there is no way to force that (feel free to correct me
if I'm wrong!).
 
¤ I have an access database on a network with more than one client accessing
¤ it. Most of the time the clients will only be reading from the database, so
¤ I want to program it to use read-only mode so that if a second client needs
¤ to write to the database, the first client won't unnecessarily prevent this
¤ simply by having an open connection.
¤
¤ I've been searching with google and in this newsgroup but I haven't come up
¤ with anything (maybe I'm not effectively choosing my search terms?). I
¤ can't believe it's as hard as it seems to find some discussion of this.
¤ Surely this is possible and surely it is commonly done, right? I'm guessing
¤ it is something I put in my connection string.
¤
¤ I am using C#, .net, and OleDb classes (OleDbDataAdapter, OleDbCommand,
¤ OleDbConnection).
¤
¤ I would appreciate any advice you all can give.

Try using the Mode argument:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\db1.mdb;Mode=Read;


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
¤ I have an access database on a network with more than one client accessing
¤ it. Most of the time the clients will only be reading from the database, so
¤ I want to program it to use read-only mode so that if a second client needs
¤ to write to the database, the first client won't unnecessarily prevent this
¤ simply by having an open connection.
¤
¤ I've been searching with google and in this newsgroup but I haven't come up
¤ with anything (maybe I'm not effectively choosing my search terms?). I
¤ can't believe it's as hard as it seems to find some discussion of this.
¤ Surely this is possible and surely it is commonly done, right? I'm guessing
¤ it is something I put in my connection string.
¤
¤ I am using C#, .net, and OleDb classes (OleDbDataAdapter, OleDbCommand,
¤ OleDbConnection).
¤
¤ I would appreciate any advice you all can give.

Try using the Mode argument:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\db1.mdb;Mode=Read;
Click to expand...

Thanks. I think that's exactly what I need. It appears to work, though the
interesting thing is I still get a .ldb file. I figured that was only
created for read-write access, but apparently not.
 
Try using the Mode argument:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\db1.mdb;Mode=Read;
Click to expand...

And now that I know what the solution is, I found an interesting suggestion
for configuring the connection string when I did a google:

Open up Notepad and save a blank file on the desktop with a .udl extension,
like whatever.udl. Close the file and double click it on the desktop to
open it. It will open up the Data Link Properties form (I was not aware of
this tool). Select your Provider and database and then close it. Open up a
new blank Notepad document and drag and drop the whatever.udl onto the new
document and it will spit out the connection string for you.
 
¤ On Thu, 26 Aug 2004 09:14:16 -0500, Paul Clement wrote:
¤
¤ >
¤ > ¤ I have an access database on a network with more than one client accessing
¤ > ¤ it. Most of the time the clients will only be reading from the database, so
¤ > ¤ I want to program it to use read-only mode so that if a second client needs
¤ > ¤ to write to the database, the first client won't unnecessarily prevent this
¤ > ¤ simply by having an open connection.
¤ > ¤
¤ > ¤ I've been searching with google and in this newsgroup but I haven't come up
¤ > ¤ with anything (maybe I'm not effectively choosing my search terms?). I
¤ > ¤ can't believe it's as hard as it seems to find some discussion of this.
¤ > ¤ Surely this is possible and surely it is commonly done, right? I'm guessing
¤ > ¤ it is something I put in my connection string.
¤ > ¤
¤ > ¤ I am using C#, .net, and OleDb classes (OleDbDataAdapter, OleDbCommand,
¤ > ¤ OleDbConnection).
¤ > ¤
¤ > ¤ I would appreciate any advice you all can give.
¤ >
¤ > Try using the Mode argument:
¤ >
¤ > Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\My Documents\db1.mdb;Mode=Read;
¤
¤ Thanks. I think that's exactly what I need. It appears to work, though the
¤ interesting thing is I still get a .ldb file. I figured that was only
¤ created for read-write access, but apparently not.

The .ldb file is always created unless you open the database for read-only *and* exclusive access.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top