Maximum number of PT Queries

Question

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.

This seems half SQL Server half Access question, but I'm more towards finding a right approach towards Pass-Through queries and that's why posting in Access thread.

Thanks,
K

Answer 1

Hi KhurramKZ -

For what it's worth, I've had zero problem's on the SQL Server side of connection management. I don't have a problem when I let Access manage this. However, I have made certain to close connections when I retrieve recordset(s) using vba because I've occasionally encountered a message to the effect of too many connections open - by Access, not SQL server. But that's not what your asking.

I'm sure you've done your research, but 32,767 is the number for SQL Server 2016 mentioned in https://msdn.microsoft.com/en-us/library/ms187030.aspx

Full capacities are in this link: https://msdn.microsoft.com/en-us/library/ms143432.aspx

---

-MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

Answer 2

Thank you for clearing this. I'm posted this question because I'm getting wrong results when I run 8th PT query while already setup as a row source of selection form. It always shows the result from previous correct run.

Even if i close all the other forms including the selection form, it behaves as above. I'm closing connection to every query in procedure once it is called and fed the row sources of several combo boxes.

Only time it pops up correct result is when i start the application, paste the code into unbound module and run it the first thing. Once i start working with other forms, it again start giving wrong result.

The result sets of row sources are large for few combo boxes and don't want to use DLookUp because it'll pull full data over the wires. That's why I'm trying hard to make PT work.

Below is the three lines of problem code.

vTemp = Date & " 00:00:00.000"
Call RunPassThroughSELECT8("SELECT Count(ID) FROM tblEmployee_WorkPack WHERE EmployeeID = 9 AND Assignment_Date = '" & vTemp & "'")
DoCmd.OpenQuery "qrySQLPass8"

Regards,

Answer 3

Makes sense to put some of your processing on the Server end as you are doing.

A couple questions:
What does RunPassThroughSELECT8 do for you?

I'm taking a guess here, but does RunPassThroughSELECT8 set the SQL statement of qrySQLPass8 and then you expect to see that it is working in the docmd.openquery("qrySQLPass8")?

If so, the Where clause of qrySQLPass8 is not changed, so the only result would be if the Count(ID) would increase for that [Exact] date:Time & EmployeeID in tblEmployee_WorkPack. Also; if the Assignment_Date can be set with a time component, then you are not likely to get a different result, either.

---

-MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

Answer 4

The connections are pooled and cached and shared. So 1 or 20 tables – you still using the one connection.

And I not sure I would use pass-through for all of the combo boxes? This sounds very messy and will cost you whacks of code.

Just create a few views sql side to prevent any table locking, and then ensure these views are linked as tables in Access.

Once  you done the above then these few needed tables can be used directly in code for the combo boxes – nothing more is required.

Combo box 1 – choose a country

Combo box 2 – choose a city (restricted by country – combo1)

So combo box 1 could be bound to a view that has the counties – thus no pass-through requited. And it not clear if different tables are involed for each combo box, but it really does not matter.

In the after update of combo box 1, we go

   Dim strSQL     As String
  
   strSQL = "select id, City from tblCities where country = '" & Me.Combo1 & "'"
  
   Me.Combo2.RowSource = strSQL

That is it! No messy code, no messy pass-though, no connection stuff in code. Just a simple sql assign to the next combo box. just use the above type code for each additional combo cascade (the above code simply goes in each combo box after update event).

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

Answer 5

Thank you for valuable advice. Will do so.

Answer 6

Ya you're correct in your guess. Got your point, thank you.