Thoughts: Binding Data to Controls

[dazzerd]

Hi all I'm new to the boards, so hello.

I've been using Access for about 5 years now to create some quite complex multi-user EUC applications.

Regarding "databinding" in the not .net sense I was always taught that in MS Access you should never directly bind say a combobox or a listbox to a query nor to a backend table. Hence I have always populated the information to be shown in these controls from an array and if I really felt the need from a dictionary object. The point being I believe that binding to queries or direct to tables always carries the penalty of a connection being permanently open to the backend file, a problem when multiple users are trying to connect to the same backend.

Any changes to the objects would be reflected in an underlying class and when ready would be sent back to the backend table.

For a completely different reason I have had to learn about pass-through queries very recently, and I was pleasantly surprised to discover that this is basically a way to run a tSQL query direct on the server. As far as I know anything you can do in tSQL you can do via a pass-through query which expands the possibilities of Access quite a bit.

I know tSQL very well so the transition was easy what pushed me to use this option was pure performance. It is vastly quicker running a pass-through query than one using Jet, even I found should you wish to wrap a Jet query around a pass-through query which was another pleasant surprise.

Anyway this got me thinking about setting the rowsource property of say a combobox to a pass-through query. Which works perfectly fine. However not knowing much about the technical aspects of how SQL server works am I again in essence creating a permanent connection to the server (like using an Access backend) or is something else going on? Even if I am creating a permanent connection is this such a bad thing with SQL server?

I only ask it will save on a little bit of code (I can get rid of my functions that populate these controls) and it will save some time too, as well as providing some performance benefits, especially for big datasets going into listboxes.

 

[gemma-the-husky]

there is no reason not to bind a cbobox to a query. You want to avoid fetching too many items into the cbobox, but that is a different issue.

The connection is only active for the duration of the life of the form.

Binding to a query is better than binding directly to a table, but only because it is easier to filter/sort the query order. A table of say US States, would probably always be ordered correctly, and a query for that purpose might be superfluous.

 

[dazzerd]

OK that kind of answers my question indirectly.

The point I was making was whether or not having a current connection open to the backend on a concurrent database is a good thing or bad thing.

For instance we have an split application which is used by 100+ users. It is completely unbound and grabs all the data it needs at the start, holds them in arrays and populates the controls from there.

If I was to use bound controls and everyone was logged in at the same then there would be 100+ users connected to the backend. Using an access backend this is no doubt a bad thing, hence the reason we didn't use bound controls.

However if I was to use a SQL backend would it be such a bad thing?

 

[gemma-the-husky]

I think that performance with large numbers of users depends more on the activity they are doing.

Managing unbound apps is much harder than bound apps - I think most of us would stick with bound apps until there was a good reason not to.

If you are editing data, you still have to managing the synchronisation of the reads and writes, and you no longer have the access dbs manager helping you mange your data. Sounds a nightmare.

100 users may be pushing access, but it depends exactly what they are doing, and how the dbs is written.