Network Efficiency

[perryche@yahoo.com]

I have read many of the posts here, but I want to ask one more time,
what can an non-network controlling person like me, be able to write a
Access DB that can be more responsive (load and look up query faster)?

I know having a better network (faster), increasing RAM, goto SQL
server... are not options for me at my work. It will take much more
than what I am able to change there. Therefore, the only options I
have are: split the DB to FE and BE, Break up Table to smaller ones,
Use VB codes that have an error handler, what else?

Sincerely,
Perry

[salad]

Re: Network Efficiency

perryche@yahoo. com wrote:

I have read many of the posts here, but I want to ask one more time,
what can an non-network controlling person like me, be able to write a
Access DB that can be more responsive (load and look up query faster)?
>
I know having a better network (faster), increasing RAM, goto SQL
server... are not options for me at my work. It will take much more
than what I am able to change there. Therefore, the only options I
have are: split the DB to FE and BE, Break up Table to smaller ones,
Use VB codes that have an error handler, what else?

I don't believe any of the things you listed will radically change the
speed of your application. Increasing RAM (you don't say what you
currently have) is a fairly cheap upgrade. A good network card is
always nice (if you have a 10meg, get 100 meg), but that again is
hardware and network which you don't control.

What else? Make sure you use indexes on your tables. Or consider the
fact that if you have a crappy network that's down more than up, it's
possible the application you are working on won't receive much respect
no matter what you do.

>
Sincerely,
Perry
>

[DFS]

Re: Network Efficiency

perryche@yahoo. com wrote:

I have read many of the posts here, but I want to ask one more time,
what can an non-network controlling person like me, be able to write a
Access DB that can be more responsive (load and look up query faster)?
>
I know having a better network (faster), increasing RAM, goto SQL
server... are not options for me at my work. It will take much more
than what I am able to change there. Therefore, the only options I
have are: split the DB to FE and BE, Break up Table to smaller ones,
Use VB codes that have an error handler, what else?

Few things that might help:

Proper column indexing, of course. Use the minimum field sizes required.
Transfer the minimum amount of data necessary: both rows and columns. Don't
use SELECT * if you can help it. Sort on indexed fields. Don't bind forms
(with SourceObject) until they're used. Set form Recordset Type to Snapshot
if possible. Open recordsets in code with dbForwardOnly or Snapshot option
if possible. Populate local lookup tables in the FE. Don't use subqueries
(SELECT * FROM TABLE WHERE FIELD IN (SELECT FIELD FROM...)).

Sincerely,
Perry

[David W. Fenton]

Re: Network Efficiency

perryche@yahoo. com wrote in
news:1153273248 .329538.143490@ 75g2000cwc.goog legroups.com:

I know having a better network (faster), increasing RAM, goto SQL
server... are not options for me at my work. It will take much
more than what I am able to change there. Therefore, the only
options I have are: split the DB to FE and BE, Break up Table to
smaller ones, Use VB codes that have an error handler, what else?

The main thing is to request and load only the smallest data set
that can satisfy a user's needs. That means for regular forms, one
record at a time, for continuous forms as subforms, only the records
belonging to the parent record, for combo- and listboxes, fewer than
100 records, with rowsource assigned only after a couple of
keystrokes are typed when you have more than 100 or so records in
the table it's based on.

That's just for starters. The main thing is:

Never bind anything with a recordsource/rowsource to a full table --
always filter to the smallest dataset possible.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[perryche@yahoo.com]

Re: Network Efficiency

Thank you all for your insight. I got some of your points while others
I am not sure what steps to do... since I an pretty green in Access VB
coding.

"regular forms, one record at a time, for continuous forms as subforms,
only the records
belonging to the parent record,

>How do I control to open one record at a time? Isn't it usually is, anyhow?

for combo- and listboxes, fewer than
100 records, with rowsource assigned only after a couple of
keystrokes are typed when you have more than 100 or so records in
the table it's based on. "

This is interesting, again, how do I accomplish this?

Sincerely,
Perry

[David W. Fenton]

Re: Network Efficiency

perryche@yahoo. com wrote in
news:1153446908 .424900.314240@ s13g2000cwa.goo glegroups.com:

Thank you all for your insight. I got some of your points while
others I am not sure what steps to do... since I an pretty green
in Access VB coding.
>
"regular forms, one record at a time, for continuous forms as
subforms, only the records
belonging to the parent record,
>

>>How do I control to open one record at a time? Isn't it usually
>>is, anyhow?

The default behavior in Access is to bind a form to an entire table,
though only one record at a time is displayed. The way to be
efficient is to bind the form to only one record (or a small group
of records) at a time. You do this by setting the WHERE clause of
the form's recordsource. You then provide a FIND facility on the
form so users can locate the records they want.

for combo- and listboxes, fewer than
100 records, with rowsource assigned only after a couple of
keystrokes are typed when you have more than 100 or so records in
the table it's based on. "
>
This is interesting, again, how do I accomplish this?

For a combo box, don't assign the rowsource until after a couple of
characters have been typed, and then filter the rowsource with a
WHERE clause using a LIKE on the characters already typed. You would
use the OnChange event to test the length of what has been typed and
then assign the rowsource at the point the desired length is
reached. There are some other details, like not having a blank
rowsource, but setting the rowsource in the form's OnCurrent event
so that it can display any existing values.

For listboxes, it's a little more complicated, as there's no
AutoComplete. You'd basically want to replicate a combo box with an
unbound textbox that filters the listbox.

I don't use listboxes in
these circumstances, so have never done it, but it's basically
exactly the same process with minor changes to account for two
controls instead of one. I find that when listboxes are needed, you
can basically obviate that need by using a combo box and dropping
down the list when the first keystroke is typed (or when the
rowsource is assigned). At that point, it's pretty much
indistinguishab le from a listbox.

For either of these, if you don't know how to do them, ask and
someone will no doubt provide the details.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[perryche@yahoo.com]

Re: Network Efficiency

Sincere thank you to David. I have no idea on how to do anything such,
seems like reequiring some VB programmings. Well, I still appreciate
your insight, something for me to look forward to learning. Thanks
again.

Sincerely,
Perry

[David W. Fenton]

Re: Network Efficiency

perryche@yahoo. com wrote in
news:1153605806 .153127.149000@ b28g2000cwb.goo glegroups.com:

Sincere thank you to David. I have no idea on how to do anything
such, seems like reequiring some VB programmings. Well, I still
appreciate your insight, something for me to look forward to
learning.

Yes, you do need to program to make an application network
efficient. Most of the default behaviors objects created with the
easy-to-use Access point-and-click interface are going to be *very*
inefficient, unfortunately. But you can use those as the starting
point and then code to make them efficient.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/