Limit Connections

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

Is it possible via code or within a access database to limit the number of
connections involved with the database.

I have a client who has asked me to write a program but is being dishonest
(told by other sources) on the number of users involved.

I do vb.net and windows applications.
 
Hi,

What database server are you talking about?
If you are talking about Sql server, I guess you might look into one of the
system stored procedure to see how many connections are alive or something
like that.
 
If you are talking about Sql server,

Unfortunately no.

It is an access database specifically 2000. He is going to locate it on a
network source which I built him the funcitonality to do but he told me
there was going to be 6 users, which is what I had based my fee on. As it
turns out there is going to be about 30. Any ideas on limiting connections
would be great. I still have a while before I have to deploy so I am open to
solutions.
 
Hi,

The only option I can think of is that you create shared data access tier
and route all functionality through it.
 
The only option I can think of is that you create shared data access tier
and route all functionality through it.
Click to expand...

Will you show me or link me to an example of this please?
 
Is it possible via code or within a access database to limit the number of
connections involved with the database.
Click to expand...

Not sure about .net but in ADO you can call the OpenSchema method to find
out how many are currently connected.

See thread (change the example from Delphi to VB):
http://81.130.213.94/myforum/forum_topics.asp?FID=10&PN=1

I & others would be interested to know if this works for you...
--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
http://www.adoanywhere.com/forum
 
The joke's on your client if they are going to have 30 users in the database
at the same time. I wouldn't even advise six, really--especially if one of
the client machines is non-NT.

While Access works very well for single (and sometimes few) users, you will
almost certainly end up with corrupted data with too many users.

This is because every client is accessing the database as a file directly.
And apparently this partial-file-locking mechanism gets even worse with
Win9X-based machines (or at least this has been my experience).

So if you think your client is going to be using 30 users at once, then an
evil (yet funny) thing to do is to let them use your software. When they
come to you complaining of corrupted data, tell them you only licensed them
for 6 because that's all you thought Access could handle.

Then tell them that an MSDE or SQL-Server based version is available that
can easily handle his 30 users, if they are willing to pay the PROPER
license charge.

--Matthew W. Jackson
 
The joke's on your client if they are going to have 30 users in the
database
at the same time. I wouldn't even advise six, really--especially if one of
the client machines is non-NT.
Click to expand...

I am aware of the potential that exists for data corruption within an access
database through MS advisories.

What I have seen is actually different.

I have seen up to 30 users use the database without a problem - it appears
that where developers run into problems is if their connection strings are
incorrect.

Of course this is based on my own experience. Matt may well be right. I have
not read it anywhere. It is based only on my own experience.

Do you have a link on how we could use MSDE in a networked situation as I am
describing?
 
What is lpszUserBuffer and nOptions suppose to be in your opinion? MSLDBUSR
apprently comes with the "JetUtility" at that link.

Declare Function LDBUser_GetUsers Lib "MSLDBUSR.DLL" (ByVal lpszUserBuffer()
As String, ByVal lpszFilename As String, ByVal nOptions As Long) As Integer
Declare Function LDBUser_GetError Lib "MSLDBUSR.DLL" (ByVal nErrorNo As
Long) As String

Public Const OptAllLDBUsers = &H1
Public Const OptLDBLoggedUsers = &H2
Public Const OptLDBCorruptUsers = &H4
Public Const OptLDBUserCount = &H8
Public Const OptLDBUserAuthor = &HB0B
 
I am aware of the potential that exists for data corruption within an
access
database through MS advisories.

What I have seen is actually different.
Click to expand...
I have seen up to 30 users use the database without a problem - it appears
that where developers run into problems is if their connection strings are
incorrect.
Click to expand...

You're a luckier man than I. All it takes is for a user to turn off their
computer during a write operation and your database can be completely wiped
out.

Also, depending on the operating system in which the .MDB file resides, your
client may need to install the latest service pack. This shouldn't be a
problem, but I have seen companies that do a horrible job of keeping their
software up to date.
Of course this is based on my own experience. Matt may well be right. I have
not read it anywhere. It is based only on my own experience.
Click to expand...

I can't be sure exactly how many *users* were in the databases I've seen
corrupted, because the software in question had a nasty habit of sometimes
making more connections than it actually needed. During coding the
programmers on the project just assumed connection pooling would fix the
problem, but it never seemed to work. I'd really love to figure out what
was going on, but that project has since been moved to .NET and a
disconnected data model and MSDE.
Do you have a link on how we could use MSDE in a networked situation as I am
describing?
Click to expand...

Sure.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmsde/html/msderoadmap.asp

Since MSDE is just a lightweight version of SQL Server, it has no problem in
networked situation. It uses the same port and protocol as SQL Server, so
any program written to connect to a TCP/IP address with a SQL Server should
run fine.

The biggest problem you will come across is deployment and backup. Unlike
Access you can't just copy the file where you want. The procedure involves
disconnecting and reconnecting the database.

Or you could take an approach I've seen used and write a program to dump a
SQL/MSDE database into an Access database (and vice versa) as a backup.
This has other benefits, such as letting a single user do what-ifs with a
local copy of the data.

--Matthew W. Jackson
 
¤ Is it possible via code or within a access database to limit the number of
¤ connections involved with the database.
¤
¤ I have a client who has asked me to write a program but is being dishonest
¤ (told by other sources) on the number of users involved.
¤
¤ I do vb.net and windows applications.
¤

I don't see an OleDbSchemaGuid enum for the use of provider specific schema. You will probably need
to use ADO:

HOW TO: Check Who Logged into Database with Jet UserRoster in Access 2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;198755

The other option is to use the Jet Utilities:

ACC97: How to Read .LDB Files
http://support.microsoft.com/?kbid=186304


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