Access Connection String

[John]

Hi

What is the recommended connection string for connecting to an access
database for a networked multi-user .net app?

Thanks

Regards

 

[Scott M.]

Same as it's always been:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dataSourceHere

 

[John]

No mention of sharing or locks etc.?

Regards

Same as it's always been:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dataSourceHere

 

[Scott M.]

Sharing and record locking were (in ADO) items that were configured at the
recordset level, not necessarily in the connection string. In .NET, you
work with disconnected data (you get a copy of the data to work on locally)
so record locking and sharing are irrelevant. When you need to merge your
local changes with the master data, you accomplish this using the ADO.NET
DataAdapter class. This class allows you to write your own logic for
performing updates.

 

[John]

Thanks. How do I set the timeout for this connection? As it is access I want
to keep one connection open for the app and set its timeout to something
like 10 minutes.

Regards

 

[Scott M.]

I don't think you are getting how ADO.NET works. You get a copy of the data
and as such, you only need your connection open long enough to get that
copy. Why in the world would you want to tie up a connection for 10 minutes
when you may only need it open for .5 a second?

The rule of thumb is to call the open method of the connection object when
you are ready to start working with data and call the close method of the
connection as soon as you are done.

Also, in ADO.NET, connection pooling is used by default, so even though you
may be done with a connection object and it may have fallen out of scope, it
won't be disposed because of the pooling.

 

[Paul Clement]

¤ Hi
¤
¤ What is the recommended connection string for connecting to an access
¤ database for a networked multi-user .net app?

See the following:

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet


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

 

[John]

I know but what I am doing is bit of black magic. I have a single connection
which all my data adapters use. The problem is that if I open the connection
explicitly before any data adapter is used then all operations are much
faster than if I don not open the connection explicitly. Perhaps not the
norm but in my case due to sluggish performance with even 2-3 users I am
forced to do that and no there aren't any intensive data access operations
going on either.

Now I just need to keep this connection for a long time so the user does not
come across closed connection too often.

Thanks

Regards

 

[Cor Ligthert]

Hi Scott,

There is an advantage with an Access database with keeping the connections
open. With the connection open that database can (as far as I know) not be
replaces which is a disadvantage in my opinion from the access database.

I thought too that setting a connection to an Access database is very
slooooooooooooooow.

Cor