Proper PT Approach (1 Viewer)

[khurram7x]

I'm now using connection-less Pass-Through queries, advice from previous post. Now I can see another issue appearing, i.e. I've a boundless search form with about 7-10 selections and each previous selection provides a Row Source for next combo box. Logically this seems to me that each previous connection remains open. so if i make 8 selections i am opening 8 connections only from one front-end Access app. There will be several more people using the same Access App, once application will be in production next week and this means i'll be opening 100s of connections to SQL Server from from single SQL Server for eg 10 users using the same Search Form!!



Now, how many connections back-end SQL Server could handle?? When will it become a problem for me?? What precautions should I take while designing SQL Pass-Through queries in scenarios like this please?



Assuming that I'm using single SQL Server login because front-end Access 2010 application have it own authentication mechanism, and company wants to utilize this instead of paying licensing fee for each separate SQL Server user.

Thanks,
K

 

[HiTechCoach]

It is a per-instance, not per database, configuration.

You can check the current setting in sys.configurations and change it with sp_configure.

The relevant option is user connections:

It is common to have 1000's of connections when there are lots of active front ends (users).


Also see: Maximum Capacity Specifications for SQL Server

FWIW: I have several Access applications the use MS SQL Server Express as the back end. Each with 25+ users and millions of records. I use linked tables and lot of DAO code. Rarely a Pass-Thru query. Never had any speed issue. Tested ADO did not see any speed improvements over DAO.

Curious, why go through all the every effort to use connection-less Pass-Through queries?

 

[Galaxiom]

Assuming that I'm using single SQL Server login because front-end Access 2010 application have it own authentication mechanism, and company wants to utilize this instead of paying licensing fee for each separate SQL Server user.

That certainly won't bypass the CAL requirements so don't waste time pursuing it.