Configuring Jet connection parameters using .NET OLEDB coneection

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rahul - 17 Sep 2007 11:40 GMT

I use .NET 2.0 OLEDB provider to connect to a connection string. The MSDN
library does not enumerate & explain various connection parameters that can
be set.

I copied the following code from one of the connection string:
                "Jet OLEDB:Global Partial Bulk Ops=2;" & _
                "Jet OLEDB:Database Locking Mode=1;" & _
                "Jet OLEDB:SFP=False;persist security info=False;" & _
                "Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;" & _
                "Jet OLEDB:Create System Database=False;" & _
                "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
                "Jet OLEDB:Compact Without Replica Repair=False;" & _
                "Jet OLEDB:Global Bulk Transactions=1;"

Can anyone explain this string? I am not asking about the parameters, like
what is Locking Mode. What I am asking is whether Locking Mode=1 locks the DB
with Pessimistic or Optimistic Locking protocal?
Moreover, I need to tweak my application performance. So, can somebody point
me to a resource that enumerates & explain all parameters that cna be set in
this string???

Reply to this Message

Norman Yuan - 17 Sep 2007 15:11 GMT

Since you use .NET OLEDB (ADO.NET) to connect to jet database, there is no
need to set values of all the connection parameters (usually). ADO.NET, by
its nature, is an disconnected data access approach. There is no such
concept as ADO's Pessimistic/Optimistic RecordSet locking. In ADO.NET you
either use DataReader, which is read only and forward only, (or Data
Adapter, which use DataReader inside) to connect your disconnected dataSet
to data source, or you use OleDbCommand to uodate data source. So, unless
you application really need do something that in turn needs to set
particular connection parameter, a simple connectionString,

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myPath\myJet.mdb;" & _
"User ID=Admin;" & _
"Password="

would do, in most cases.

Since no details on what exactly your app does and what specific connection
parameter your app must configure, can't say more on this.

>I use .NET 2.0 OLEDB provider to connect to a connection string. The MSDN
> library does not enumerate & explain various connection parameters that
[quoted text clipped - 20 lines]
> in
> this string???

Reply to this Message

Rahul - 17 Sep 2007 15:44 GMT

> Since you use .NET OLEDB (ADO.NET) to connect to jet database, there is no
> need to set values of all the connection parameters (usually). ADO.NET, by
> its nature, is an disconnected data access approach. There is no such
> concept as ADO's Pessimistic/Optimistic RecordSet locking.

So, if two users try to access DB simultaneously, the complete DB file is
served to both of them. In the absence of a locking mechanism, if they apply
conflicting upgrades to it, how the consistency is maintained then???
Moreover, I was talking about using parameters to set locking mode for the
underlying JET engine for my DB, not ADO's locking as there obviously is no
locking for a code library!!!

a simple connectionString,

> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=C:\myPath\myJet.mdb;" & _
> "User ID=Admin;" & _
> "Password="
>
> would do, in most cases.

Well, I was unable to win-over the security mechanism of Access. So, I
implemented custom security mechanism using an encrypted User table in the DB.

Well, some of the parameters I want to cinfigure that I recall at this time
are Page/Record Level Locking, Lock Retry etc.
Anyways, I found the following resource at MSDN:
http://msdn2.microsoft.com/en-us/library/aa140022(office.10).aspx

Now, my intention is to maximize transaction throughput for my application,
where a transaction on an average consists of 2-4 updations.
So, now what I am looking at is a resource to advice me to maximize
transaction throughput for Access (actually for the JET engine)!!!

Reply to this Message

Norman Yuan - 17 Sep 2007 18:24 GMT

>> Since you use .NET OLEDB (ADO.NET) to connect to jet database, there is
>> no
[quoted text clipped - 11 lines]
> no
> locking for a code library!!!

ADO.NET, as I mentioned, is disconnected data access sulotion. It uses
"Optimistic concurrency" approach to resolve data entey conflict. Further
discussion on this topic is out of topic of this NG, you may want to post to
NGs with ".NET" in their name. If you want lock of ADO or DAO RecordSet
does, then simply develop your app in MS Acess.

> a simple connectionString,
>>
[quoted text clipped - 20 lines]
> So, now what I am looking at is a resource to advice me to maximize
> transaction throughput for Access (actually for the JET engine)!!!

Reply to this Message