SQL Open Connections and MS Access front end

Larry Brister
Larry Brister used Ask the Experts™
on
I am new to our company which is using MS Access as their interface with SQL database instead of Access tables. I've been brought in to move everything to Intranet Web Application.

The company has 80 employees of which about 70% at any particular time are on the MS Access system.

When I run my code below...a standard when I was checking my intranet site(s) at my previous company...I'm getting minimun of 280 and sometimes 350+ connections to the application database. For max of 60 people using system + 4 developers + any job(s) that may be running.

Is this normal for MS Access Applications?

Generally speaking on my intranet sites as I opened and closed connections I would see a maximum of 25-40 connections in a company of 250 + people. And 6 of those were my development team.


Thanks

Getting open connections
SELECT DB_NAME(dbid) as 'DbName', COUNT(dbid) as 'Connections'
   from master.dbo.sysprocesses with (nolock)
   WHERE dbid > 0 and DB_NAME(dbid) not in ('master','msdb')
   GROUP BY dbid
   ORDER BY DB_NAME(dbid)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ishaan RawatDesigner | Developer | Programmer

Commented:
Yes. It is normal
Top Expert 2015
Commented:
One instance of Access might open more than one connection at a time. This is specially true if you are using .mdb files with linked tables instead of .adp files.

.mdb files are the default type of files when creating with Access, and since they can link simulatneously with almost anything that is data (that is one of the forces of Access), they are not optimized to work with any particular external database.

.adp files are an alternative. This is a mode that is designed specifically for SQL Server and thus can handle things in a better way.

If there is some VBA code in the Access front end, that code might also open more than one connection. And if those are not handled properly, those connections might remain opened when not in use.
I know you're planning to move everything to a web app, and that can be a lot of work.  Just so you know, a well-designed Access + SQL Server application can easily handle 50-70 users.  But it can't just used linked tables for everything - there are specific techniques that must be used.

I've written a PowerPoint presentation on this.  It's called "Best of Both Worlds" at our free J Street Downloads Page:

http://www.JStreetTech.com/downloads

It includes some thoughts on when to use SQL Server, performance and security considerations, concurrency approaches, and techniques to help everything run smoothly.

Cheers,
Armen Stein
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2015

Commented:
If the thing is going to move to the web, I do not see why Access should stay in the equation. Access used with SQL Server is only a front end. If the front end becomes a web site, Access becomes just a useless bridge that will eventually just be a handicap. The proper route would be to go straight from ASP to SQL Server.
Hi James,

I don't think lrbrister is saying that Access will still be part of the new system.  They're going to replace it with a web app.  The question is about how the old Access app is using SQL Server connections, if I understand it correctly.  I just added that it's possible to use an Access/SQL app with that many users if it's done correctly.

Armen
Larry Bristersr. Developer

Author

Commented:
THis put me on the right track

ADP is being used with some vba...however...connections were being left unnecessarily open.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial