B
Babbage
Hi there all!
In basic terms - anyone know how to stop combo boxes from `holding open`
connections to a database that its using to populate its rows (or put another
way, force a combo box based on large datasets to fully populate itself when
a form opens)
.........................
In more detail -
My form has combo boxes which have standard sql statements to a MS SQL 2000
server backend for its datasource. There may be 1000's of records in this.
having smaller recordsets is not an option.
When you first open a form, combo boxes connect to the db and start to draw
down data. However, with these combo boxes with lots of rows, not all the
data is brought down at this point, just the rows so far displayed. You can
see this manifest in the combo box by trying to jump down the combo box using
the standard scroll bar, only you don't get scrolled to the last record, as
more rows populate on the fly, and the scroll bar position jumps around.
Once youve managed to reach the bottom, all rows are populated and you can
scroll up and down perfectly.
Meanwhile, on the sql server, while the combo box hasnt yet got down all its
data, it holds an open connection. This connection stays until the combo box
is fully populated.
This open connection is of a type _not sharable_ on the sql server = BIG
PROBLEMS FOR ME! (I.e. sql server isnt allowing multiple requests to run down
the same connection, as it does for other connection types)
The way this kicks dirt in my face is described in the following scenarios....
- We start up 200 machines with an access front end client. All 200 machines
connect to the database, do their stuff, then sit there waiting for users to
start using them. Fine.
- We start up 220 machines, and things start to fail. All existing machines
connected to the database get timeouts and everything falls appart.
- We start up 220 machines very slooowly, getting users going on them as and
when, and eventually all are up and running. Fine!
- I ran a test with a couple of thousand general data connections all
hitting the database at the same time, just to see what would happen. Ran
fine
- I had a careful watch on the server. What was happening was all the
machines were loading up, connecting to the server, doing their stuff,
loading a default form which had combo boxes on which were opening
connections to the database that stayed open (waiting for the combo box to
request all its data).
Once there were too many of these type connections (at a critical point
where around 220 machines hit this point with our particular setup) the SQL
server runs out of available connections and everything goes `tits up`.
Logging in everyone bit by bit allowed users to make sure their combo boxes
were fully populated, which then dropped their connections freeing up
connection spaces.
We got around this problem at the time by changing the number of connections
sql server allowed from the default 255 I think it was and upping it to 1000.
To me thats a work around rather than a solution.
SO!
My question is - does anyone know how to force a combo box to make sure its
brought down all of its data/is fully populated when first opened. I cant
think of a way how!
Many thanks
martin
In basic terms - anyone know how to stop combo boxes from `holding open`
connections to a database that its using to populate its rows (or put another
way, force a combo box based on large datasets to fully populate itself when
a form opens)
.........................
In more detail -
My form has combo boxes which have standard sql statements to a MS SQL 2000
server backend for its datasource. There may be 1000's of records in this.
having smaller recordsets is not an option.
When you first open a form, combo boxes connect to the db and start to draw
down data. However, with these combo boxes with lots of rows, not all the
data is brought down at this point, just the rows so far displayed. You can
see this manifest in the combo box by trying to jump down the combo box using
the standard scroll bar, only you don't get scrolled to the last record, as
more rows populate on the fly, and the scroll bar position jumps around.
Once youve managed to reach the bottom, all rows are populated and you can
scroll up and down perfectly.
Meanwhile, on the sql server, while the combo box hasnt yet got down all its
data, it holds an open connection. This connection stays until the combo box
is fully populated.
This open connection is of a type _not sharable_ on the sql server = BIG
PROBLEMS FOR ME! (I.e. sql server isnt allowing multiple requests to run down
the same connection, as it does for other connection types)
The way this kicks dirt in my face is described in the following scenarios....
- We start up 200 machines with an access front end client. All 200 machines
connect to the database, do their stuff, then sit there waiting for users to
start using them. Fine.
- We start up 220 machines, and things start to fail. All existing machines
connected to the database get timeouts and everything falls appart.
- We start up 220 machines very slooowly, getting users going on them as and
when, and eventually all are up and running. Fine!
- I ran a test with a couple of thousand general data connections all
hitting the database at the same time, just to see what would happen. Ran
fine
- I had a careful watch on the server. What was happening was all the
machines were loading up, connecting to the server, doing their stuff,
loading a default form which had combo boxes on which were opening
connections to the database that stayed open (waiting for the combo box to
request all its data).
Once there were too many of these type connections (at a critical point
where around 220 machines hit this point with our particular setup) the SQL
server runs out of available connections and everything goes `tits up`.
Logging in everyone bit by bit allowed users to make sure their combo boxes
were fully populated, which then dropped their connections freeing up
connection spaces.
We got around this problem at the time by changing the number of connections
sql server allowed from the default 255 I think it was and upping it to 1000.
To me thats a work around rather than a solution.
SO!
My question is - does anyone know how to force a combo box to make sure its
brought down all of its data/is fully populated when first opened. I cant
think of a way how!
Many thanks
martin