UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Strange Situation With Available Database Connections, Any Version    
 
   
ngins
post Jan 16 2020, 10:33 AM
Post#1



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


I've been posting recently about an issue I've been having with the "Can't open any more databases" error. I recently found something strange, which I felt warranted a separate thread.

My database is a tabbed database with many subforms and tons of dropdowns -- many of the dropdowns are based on complex queries. No doubt that's what's eating up my connections. I have plans to revamp and simplify everything, which should help the problem.

For now, I'm using code from the tool that Ben Sacharich developed a while back, and which a couple of people here were kind enough to share with me. (Thank you!) It has been very useful.

I created a log of available connections, and have my database write to the log at certain key points -- when the user changes tabs, or clicks on a button, etc. Also when the main form containing the tabbed control is first opened.

The database is used in Canada, and it isolates the data by province. The user logs in by clicking on a province they want to work with; that province ID is written to a front end table; and that front end table is then used in all code and queries to restrict data to that province's data only, by using an inner join to the table. All well and good.

But here's where it gets strange.

The number of database connections used is theoretically based on tables used -- either in forms, subforms, dropdowns, queries, etc. A table with, say, 1,000 records should use the same number of database connections as a table with 100,000 records, right?

So I found, through my log, something strange: all provinces use the same code, forms, and queries. However, I noticed that when logged into one particular province (Province A), the number of available connections drops significantly lower than with Province B or C. Province A has more records; but everything else is the same between the three.

I tested this following the exact same steps when logged into one province vs logged into a different province (and closing and reopening Access between tests), and I found a difference of 30-40 connections between them.

And with general use, according to my log, the Province B and C users usually have over 100 connections available (sometimes dipping down to 80 or 90), whereas the Province A users are usually in the low 40s, and sometimes dipping down as low as 17 (before getting the error).

Indeed, it's only the Province A users that have been getting this error.

So this seems counterintuitive. If the same functions, which use the same forms, code, and queries, are being accessed each time, with the only difference being the set of data that's being accessed, shouldn't the number of connections used be the same each time? I find this puzzling.

Any thoughts on this?

Thanks!
This post has been edited by ngins: Jan 16 2020, 10:57 AM

--------------------
Neil
Accessing since '96
Go to the top of the page
 
isladogs
post Jan 16 2020, 11:10 AM
Post#2


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


As I supplied you with the Available Connections tool, I am utterly puzzled by your findings.
As you say each open connection matters whether table, query, form/report with row source objects or use of recordsets.
The number of connections 'consumed' by each differs from 1 to 6 but it shouldn't matter what the elected data is...unless the code then requires additional connections if its e.g. Province A

Whatever the causes of the above, I find it very odd that your databases use as many connections as you describe.
I've tried testing several of my 'power apps' based on Access or SQL Server BEs and none drop to the levels you describe ...even when I deliberately open lots of heavy duty forms (etc) at once.
It does sound like you are keeping a lot of objects open rather than closing them.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ngins
post Jan 16 2020, 11:34 AM
Post#3



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Yes, the db uses a lot of connections. I think the issue is mainly dropdowns which are used for searching I have at the top of my two main forms. Each form has about a dozen dropdowns, and each dropdown contains complex queries. So I think that's one of the main culprits.

Still, these results are puzzling, and shouldn't be so, according to theory. Very strange.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
isladogs
post Jan 16 2020, 01:39 PM
Post#4


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


I have some very complex multi purpose forms with multiple drop downs but they still use far fewer connections than yours.
You could try modifying those so the rowsource isn't loaded with the form but only populated when required using code...and cleared again afterwards.
If that doesn't adversely affect performance more than running out of connections, it might help.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ngins
post Jan 16 2020, 02:29 PM
Post#5



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Well, I thought of that. But I'm trying to avoid the delay of a second or two each time the user goes to a dropdown.

Each of the dropdowns pull off of the same data, which is comprised of about 5 tables and a complex subquery. So my thought is to create a front end table that stores the data for all the dropdowns to feed off of, the only difference between the dropdowns being the fields displayed and the criteria applied.

Then, when the database is opened, the table is rebuilt. If a user adds records that affect the dropdowns, then those records would be added to the front end table when they're added.

Then I can easily populate the dropdowns when they're used, and they would load instantly. Or I could leave them in place, since they'd all be pulling off of a single table only, rather than a complex query.

The only drawback to this would be if another user needs to look up an item that was recently added by another user. They wouldn't have that data. But that would be rare. Still, it would be a potential problem.

An alternative would be to put the table in the back end instead, which would work fine, though it would be a tad bit slower. Still, that might be a better solution.

Or, alternatively, I could take all the dropdowns, remove them from the form header, and instead put them into a pop-up form, forcing the user to click a button to open the form, and then close it afterwards. I try to avoid having the user perform extra clicks like that. But that would free up the connections when the form is closed.

So those are my thoughts on the matter.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
ngins
post Jan 16 2020, 03:01 PM
Post#6



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


(continuing)

Just tested removing the row source query from one of the dropdowns. Net gain from having no query in the rowsource was 17 connections!
This post has been edited by ngins: Jan 16 2020, 03:14 PM

--------------------
Neil
Accessing since '96
Go to the top of the page
 
isladogs
post Jan 16 2020, 03:29 PM
Post#7


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


Wow! Even more than I expected. Worth continuing with the process I'd say!

Is the row source more complicated than it needs to be?
Does performance suffer by loading the row source on demand? (Sorry - missed your earlier answer)

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ngins
post Jan 16 2020, 04:12 PM
Post#8



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


QUOTE
Is the row source more complicated than it needs to be?


That, my friend, would be an understatement! LOL I'm a bit embarrassed to admit this, but here goes.

So the data in the dropdowns, consists of entities, and entity owners, along with some other information related to the entity.

All the information is pretty straightforward -- several tables joined together to get various pieces of information. No big deal.

However, the entity owners is where things get complicated. An entity can have one or multiple owners, and these are stored in a child table.

When looking things up, the user is usually interested in seeing the first two owners, for reference, to make sure they have the right entity.

So, since they're stored in a child table, I created a subquery that returns the first owner for each entity. However, that's only the first owner.

To get the second owner, I had to create another query, which uses the "firstowner" query as a subquery, along with the owners table, and then returns the first owner that's not equal to the owner in "firstowner" subquery.

So this is a group by query inside of another group by query, which is inside of another query, which is used as the row source for a combo box. And there are five of these! LOL

So you can see why it was using up so many connections.

So, I tested changing this in various ways. With each test, I opened the database afresh, then switched to the appropriate tab, then clicked on the dropdown, then checked the number of connections available.

First test: current system, as a benchmark: 59 connections remaining.

2nd test: replace the double group-by query with a single group-by query (which only returns the first owner, rather than first two owners): 68 remaining.

3rd test: write the "first and second owners" query data to a table (without where conditions, so all data), and use that table instead of the query: 72 remaining.

4th test: write the entire rowsource query data to a table (without where conditions, so all data), and use that table instead of the query: 76 remaining.

5th rest: remove the rowsource altogether and have no data in the dropdown: 76 remaining.

Now that last one is kind of strange, because I would expect there to be a hit of at least one connection for having a single table as the rowsource. In fact, that was a table inside of a stored query, which was used as the rowsource, so I expected a hit of two connections -- one for the stored query, and one of the table inside of it. But, for some reason, it showed the same results as having no rowsource, which makes no sense at all. But I tested it both ways several times, and that's what I got.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
isladogs
post Jan 16 2020, 05:54 PM
Post#9


UtterAccess VIP
Posts: 2,185
Joined: 4-June 18
From: Somerset, UK


Phew! I got exhausted just reading that. You've got to feel sorry for Access trying to manage it smirk.gif
I'm amazed if doing all that performs quickly!
This may well be an occasion where using a 'temp' table would be beneficial

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
ngins
post Jan 16 2020, 06:32 PM
Post#10



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Yes, very likely. Still puzzled by how using a single table in the dropdown rowsource produced the same results as using nothing in the rowsource. One would think there'd be a difference. The mysteries abound here.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 08:08 PM
Post#11


UtterAccess Moderator
Posts: 12,325
Joined: 6-December 03
From: Telegraph Hill


One thing, Neil: are all your subforms in each tab loaded all at the same time?

A good practice if you have many tabs each with their own subform, is to use a single subform which remains visible on top and use the tab change event to load the appropriate SourceObject.

So, only ever one subform is loaded at a time. Requires a bit of extra coding though

It might go a ways to reducing your connection count while allowing you to still use your complex RowSource for the combos.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ngins
post Jan 16 2020, 08:33 PM
Post#12



Posts: 412
Joined: 18-August 05
From: DFW, TX, USA


Hi, David. Yes, I'm planning on doing something like that, but a little bit different. I have a subform control on each tab, with the forms embedded in them at all times. I'm going to remove the subform from the control , and then just replace it when the user clicks on the tab. So, similar to what you were proposing, except that each tab will still have its own subform control actually on the tab but only with a subform object when the tab is active. I find that to be a little bit cleaner than having a single subform control that floats on top of the tab control . Besides, each tab has its own needs, and sometimes there are additional controls outside of the subform making for different space configuration on each tab . So that's the approach I usually take .

So I'm gonna do that, plus I'm going to create a fixed table for the combo boxes, rather than the complex query. I'm just going to append to that fixed table, which I'll put in the back in, whenever the user adds a record. And then I'll rebuild the table from scratch as part of the nightly maintenance.

So those two things should at least get the users out of the red zone, but I'm still going to look to see what else I could change to reduce the connection count even more.

It's funny: after all these years of developing access, I never gave much thought to table connections before . I guess I've just been lucky that I never ran into the problem before, but I've always just developed without worrying about running out of connections . I guess those days are over. LOL Would be great though if access eventually increased it, even to 512.

--------------------
Neil
Accessing since '96
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:32 AM