Re: Access Form connected to SQL table in Access Data Project (.adp file)

From: John Kounis (usenet_at_pilotgetaways.com)
Date: 01/11/05 

Date: Tue, 11 Jan 2005 12:55:44 -0800

Thank you for your comments. It looks like a slight UI redesign is called
for now.

After I analyzed the use of the software, I realized that, most of the time,
one of the first things a user usually did after opening the form was to
either filter the form (by name) or add new records, so I guess I could ask
that question first.

I believe one of the changes will actually increase complexity for the user,
though. Now, since all records are available (sorted by the most recent
orders first), it's easy to navigate through the first few records to review
the ortders that came in this morning or since yesterday. The change will
require that I apply a time filter (e.g. see orders for "Today", "This
Week", or "This Month") before the form is opened. The code is not that
hard, really. It just seems to me that it will add one additional step for
the user. Under the previous design, he would just open the form and could
navigate through the last few orders by clicking "next record" a few times.

In addition, I found it nice to know how many total records were in the
database (our total subscribers). I guess I could add a function that adds a
calculated field, "Select count(*) from subscribers", somewhere, so I know
how big the database is.

Thanks again,

John Kounis

"Albert D. Kallal" <PleaseNOOOsPAMMkallal@msn.com> wrote in message
news:%23KI0V879EHA.600@TK2MSFTNGP09.phx.gbl...
> "John Kounis" <usenet@pilotgetaways.com> wrote in message
> news:OApQJD69EHA.1264@TK2MSFTNGP12.phx.gbl...
>
> >
> > I have a form that references a table with about 50,000 records in it.
> > When
> > the form was in a .mdb file, it would retrieve one record at a time and
I
> > could use the record navigation buttons or search features to navigate
> > effortlessly through all 50,000 records.
>
> Well, it sounds like you were lucky in that mdb case. If a user hits
> ctrl-f, and does a find on a field that is not indexed, then even with a
mdb
> file, you can wind up dragging 50,000 records across the network. It is
> simply bad design to open up a form to a HUGE table, and then let the
users
> "here..have at it!!"
>
> > As far as I can tell, the only way to do this with an MS Access form/
.adp
> > file front end/SQL server backend is to make a table with unbound
controls
> > and to create a recordset programatically.
>
> No, not all. You can use what is called the "where" clause. You can also
> just stuff the sql right into the forms reordsouce (see the example latter
> in this post).
>
> Think about how software works, and how your system should work. Can you
> imagine if the designers of a instant teller machine downloaded every
> single account, and THEN asked the user what account number to look for ?
>
> It makes NO sense to attach a form to a HUGE table, and THEN figure out
what
> the user wants? At what point do you control, or tell the system how much
> data, or how much of that table gets loaded into the form? YOU ARE the one
> that needs to take control of this issue in YOUR designs. YOU have to tell
> the form what records it should load. You can't leave this up to chance!
>
> Even with a mdb file, you can run into all kinds of bad things..and it is
> consider a poor design to just throw a form to a user with a large
> number of records.
>
> The fact that you can attached a form to a large table is simply a freedom
> that you as a developer have, but that freedom is simply a long rope in
> which
> to hang your self with. The reason why SO MANY developers and people think
> that ms-access performs slow is not because it is slower then VB, or c++.
> The problem is that the developers of VB or c++ would NEVER EVER load up a
> form with a zillion records, and THEN ask the user what record they
want!!!.
>
> I have applications that run all day with SMALL TABLES of 50,000 to
100,000
> records. And, these systems have 5 to 6 users working at the same time on
> this system. And, we are NOT even using sql server yet. In ALL of these
> cases, I NEVER load up a form to table without using the "where" clause.
> Response time for these systems even with 100,000 records is INSTANT in
this
> case. And,
> sql server likely would be even faster!!! (but, with good designs...we
don't
> even have to move up to sql server to get even better performance!!).
>
> So, use the "where" clause of the open form to restrict the form.
>
> Both ADP, and mdb forms have what is called a "where" clause.
>
> If you simply ask the user what they want BEFORE you load up the form, and
> then open up the form with one record, you save 10,000 times. That means
> that
> my design is not saving 10% in bandwidth, or even 10 times the amount..but
> 10,000 times faster!!! This is such a huge order of amounts, that is hard
> to not notice!
>
> Sql server is a heavy duty industrial strength database system capable
> of handling 100's of users. But, it certainly not such a heaving lifting
> system if every user tries to load up and waste the loading of 10,000
> records. What would happen if you have 20 users? (and, 20 users
> is not that many!!). Worse, you load all those records..and your user
> has not even done anything useful yet!!!
>
> So, the simply solution here is to ask the user first. For example, to ask
a
> user for a invoice number we could go:
>
> strInvoiceNum = inputbox ("what invoice to edit")
>
> docmd.openForm "frmEditInvoice",,,"InvoiceNum = " & strInvoiceNum
>
> So, I count two lines of code here.
>
> For any form that is going to be attached to a large table, you need to
> build a prompt form, and ask the user what record to work on, load the
form
> with the ONE record. When the user is done, they close the form, and you
are
> now back ready to deal with the next customer, or whatever.
>
> If you want some ideas for a nice interface, then you can read the
following
> of mine, this approach works great for both JET, and sql server. (and, for
> both JET, and sql server, the result is a reduction in bandwidth by 1000's
> of
> times..and thus your application runs 1000's of times better!).
>
> http://www.members.shaw.ca/AlbertKallal/Search/index.html
>
> And, the above search example actually does not use the "where" clause,
but
> simply stuffs in the sql in the after update event of the text box on the
> search form (earlier in this post I mentioned a 2nd approach that does not
> use
> the "where" clause, and the above is an example of where I just stuff in
the
> sql right into the forms reocrdsouce to create the "pick list"). The code
> used in the above screen shots looks like:
>
> strSql = "select * from table customer where " & _
> " LastName like '" & me!txtSeach & "*'"
>
> me.MySubForm.form.recordSource = strSql
>
> >
> > Is this true? This seems like such a fundemental function--reading one
> > record at a time from a table in an SQL database--that I am surprised
> > there
> > isn't an easier way to do it. Am I missing something?
>
> It is the up to the developer of the software to restrict those records
for
> the form, and I gave you two examples of how to do this (and, there are
even
> other ways also!!
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
>
>
>