SQL Svr and Master Child Link

[Guest]

I am trying to convert an MDB FE with a connection to an MDB BE (MDB/MDB) to
be an MDB FE with a SQL Server BE (MDB/SQL).

I have many instances of forms that each have a subform. The Record source
of the Sub Form is an UNfiltered query or a table. The records that are
displayed are automatically filtered by a Master/Child link scenario, and the
result is a very quick display of data in the MDB/MDB solution

As I convert the BE to SQL Server, I want, of course, to limit the amount of
data retrieved to only that necessary for each display, so can you tell me
what happens in the scenario described above. Are all the records returned
from the server, with only those filtered by the Master Child link shown in
the display, or is it only the records that meet the Master/Child link filter
that get retrieved?

In other words, where is the filter applied? At the BE or at the FE?

If the filter is applied at the FE, what steps do I need to take, with the
least amount of changes, or rather, so that the solution is applied in both
cases?

I will need to eventually do a replication/synchronization of the SQL BE so
that selected users can work in an environment where they do not have a
viable connection path to the server while working. I do not want to make an
adjustment to solve one problem and end up creating another, and I certainly
do not want to have to maintain two sets of code. I know I can use a switch
based upon type of connection and have both types of code/forms in my FE, but
prefer to use this method only as a last resort.

 

[Albert D.Kallal]

If you are asking do all child records get returned, the answer is no.

The real issue here is not the 5 lines of detail records that get retuned
for the sub-form, but in fact how do you limit the records loaded to the
MAIN form?

So, laving the sub-form bound to a table is fine. And, leaving the main form
bound to a table is fine also.

However, when you open that main form, I assume you are using a "where"
clause to restrict the form to the ONE main record that you need to edit.

A good example of a search screen idea is here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

So, to limit bandwidth, you need to focus on the main form.

So, to answer your question, the filter(s) will limit records transferred,
but it is still up to you to ensure that the "main" form does simply open up
to a large table.

In simple English, this means you need to ASK the user what record they want
BEFORE YOU LOAD the main form. The child records transmitted will only be
the child records needed for the one record.

It turns out that a good design for a JET based back end that limits records
loaded also then convents WELL to sql server for a back end. If your designs
did not limit records in the JET back end design, then some extra efforts
should be taken to limit records transferred.

I got a good number of multi-user applications in the 4-8 user range. The
tables are very small, usually only in the 50,000 to 75,000 record range.
With 4-8 users on a typical office network, then form load time is instant,
and no delays are generally seen. However, with such small tables, and low
numbers of users, I would not expect to see much of a delay. I think I got a
LONG way to go before I every need up grade the back end to sql server.

It does need pointing out that upgrading to sql server will NOT necessary
improve performance UNLESS you make efforts to reduce the bandwidth..

 

[Guest]

I have 2000 records in the table associated with the main form. The query in
the record source uses a WHERE clause that restricts the records the user
wants to look at. The user makes this choice by selecting the WHERE argument
from a drop down. Let's assume the WHERE clause limits the number of records
to 25. Are you saying the query retrieves 25 records to the local machine
even though I am only viewing one record. What purpose do the record
navigator buttons (First, Previous, Next and Last) serve, then? Does each
button cause a retrieval of 25 records, or just the first, previous, next or
last?

 

[Albert D.Kallal]

I have 2000 records in the table associated with the main form. The query
in
the record source uses a WHERE clause that restricts the records the user
wants to look at. The user makes this choice by selecting the WHERE
argument
from a drop down.

And, I have to assume the above happens BEFORE you load the form with
records, or the form has no recordsouce when you load...right?

Let's assume the WHERE clause limits the number of records
to 25. Are you saying the query retrieves 25 records to the local machine
even though I am only viewing one record.

Yes, the above is rather correct. Sometimes, ms-access is smart, and will
only view the one record, but you just asked the system for 25 records
and it should, and will give you those records. And, I suppose could
live with a design that pulls 25 records.

What purpose do the record
navigator buttons (First, Previous, Next and Last) serve, then?

Well, if you followed my advice, very few, if ANY of your main
forms will have navigation buttons anyway (they should not).
Certainly the sub-forms will have navigation, since by design
you got "many" records.

Does each
button cause a retrieval of 25 records, or just the first, previous, next
or
last?

No, each moving will not cause 25 records. What does happen is that you get
25 records loaded, and then you can MOVE THROUGH the list of records.

As mentioned, sometimes ms-access is smart, and will ONLY load the record as
you navigate. By the time you get to the 25 th record, then they all likely
will be cached.

But, no, each moving will not cause 25 records to load, but only the next
record.
(the 25 records are loaded at the start).

Of course, each movement will request the child records need for sub-forms.

And, it likely would be much better to display the 25 record result in a
grid, or pick list.

Some nice grid shots of this type of "drill" down can be seen here:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

With a pick list, the user does not need to navigate in the main form. Also,
by limiting to the form to "one" record, the user tends to have more
"closure" in terms of getting a task done. You edit the customer (perhaps
while talking on the phone). when you are done,you close the form (your data
gets saved, your brain can go on to the next task. Giving users a sense of
"closure" for a given task also reduces stress, and users are task
orientated. And, another bonus is that your data is saved right away for
other users to look at or use. (if you have a design that encourages uses to
leave the main form open, then you don't encourage users to close , and thus
you don't encourage users to save their data). I suspect that is why my
clients don't have any corruption problems...since their data is usually
saved all the time. (note that NONE of my clients in multi-user mode have
EVERY had a corruption. (a few single user ones did, and that was when a2000
just came out.. Since I upgraded them, I not had ONE corruption). If you
keep a form open to a table, then several records are in memory, and not yet
saved to disk. When you close the form, the records are saved.

It turns out that client to server also supports this approach. The problem
here is that ms-access has it roots in a "file" based system, so most users
(and even developers) tend to just throw up a big table attached to a form.
The reason why sql server tends to scale to so many users is that the
"general" approach to client software with sql server is that you load up
the one record, edit it, and then save it, and then are done. This approach
to software tends to break down the software to working with ONE "master"
recorded ((eg: ONE customer). The web also tends to support this model.

So, good bandwidth designs tend to go hand in hand with designs that allow
users to find, and eventually "EDIT" or "deal with" the one record.

The approach is also likely why I consider tables of 50,000 records so
small, and my response times are instant, and I not even using sql server
for most of my systems. And, the ones that are using sql server are only
doing so because we need the data to be shared to other systems.

Since I do have this genera approach to software design, then when I convert
my applications to work with sql server, I done the hard part already, and
my designs convert very well. So, much of what I am pointing out here is a
philosophical approach to software design.

However, look at a accounting system, or most other business applications
you use, and you will note that they tend to all follow the above concepts
from a design point of view, especially if a server database is involved.


Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal