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

[John Kounis]

I recently converted from an Access 2000 .mdb file to an Access Data Project
(.adp file)/MSDE backend. As part of the upgrade, I moved my forms from the
..mdb file to the .adp file as well.

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.

Now that the form is in an access data project, it has a max records
property that defaults to 10,000. When I open the form, it starts a huge
query to download the first 10,000 records. This has 2 problems:

(1) I really don't need all 10,000 records. I might just search through the
table to find 10 or 20, so this is an excessive load on the server.
(2) As far as I can tell, it is impossible to navigate past the 10,000 that
were downloaded. (Yes, I can increase the max records to 50,000 or 100,000,
but then it would download the whole table... again a huge load on the
server).

Ideally, I would like to only download one record at a time. Then, if I
decided to, for example, skip to orders starting on 1/1/05, I could skip
49,000 records, and start browsing there.

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. Then I can populate the form with
data from the recordset. When the user wants to navigate, I would have to
update the controls on the form after I navigate the underlying recordset. I
would also need to update the recordset when a user edits a field on the
form. This means I need to write new code that performed the features that
were automatic in the previous version: Edit Record, Delete Record, Add
Record, Find Record, Filter, Next Record, Previous Record, First Record, and
Last Record, as well as code to populate the form in the first place.

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?

John Kounis

 

[Albert D. Kallal]

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!!

 

[John Kounis]

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]

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.

Actually, the above is a good thing, since if your present them with a
search screen, the people are more likely to search for a person then
be lazy, and just add the record!!

Further, if you allow users to put their cursor in a field, and hit ctrl-f
to find, you can VERY easily bump a key, and overwrite the
contents of a field..and users will not notice. (very dangerous).

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.

I have to agree with you on the above. The fact that you think about,
and want to make small issues easy means that you REALLY do care
about your users. Great attitude on your part..and is the key to wring
software that your users will like!!

If you look closely at the search screen examples...you can in fact see a
button called "booked today" that shows people added "today".

It actually shows a "list" of bookings today, and users like that MUCH more
then having to use the navigation buttons to scroll through 5, or 10 new
entries. So, you can give a few options that will make users happy, and they
will not miss the fact that you can just scroll back to see previous
entries. (a list is faster..and they can pick the one right from the
list....).

And, you can also note that there is a button to add a new invoice grouping
(in your case..new what ever). Again, this means I don't allow users to
navigate to a new record, as that is too accidental.

Anyway..good luck!