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

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 01/11/05 

Date: Tue, 11 Jan 2005 02:22:27 -0700

"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