Re: Need some query design help....

From: T. (t_at_NOSPAM.ca)
Date: 03/11/04 

Date: Thu, 11 Mar 2004 18:31:49 GMT

Thanks much John, I appreciate your insights. Would you be able to direct
me to a good resource on design and implementation of client/server Access
solutions?

Thanks again,

T.

"John Viescas" <JohnV@nomail.please> wrote in message
news:ugmUQhwBEHA.3796@TK2MSFTNGP10.phx.gbl...
> You should have enough info at this point to work it out yourself. You
> haven't told me much about the business purpose of this application or the
> table structure, but you have indicated that you know which office has
> signed on. Ask yourself whether an office ever needs to see records from
> another office. If so, is that a common occurrence, or does a particular
> office work only with their own records?
>
> Let's say, for example, that you have Customers as a selection on your
main
> switchboard. If an office works only with its own data, when the user
> clicks that button, open the Customers form filtered to show only the ones
> for the current office. If they occasionally need to see all customers,
> take them instead to a QBF form that not only gives them the option to see
> all customers (with a warning that this is slower) but also gives them the
> option to specify common filtering criteria in addition to filtering by
> office. Make the users ask for only the records they need to perform the
> current task. For example, why show them all Orders when they only want
to
> look at the current month?
>
> Another thing to look at is "lookup" tables that don't change very often.
> For example, I often distribute a table of Zip codes, with related city,
> county, and state data with my applications. But I make that a local
table
> in each MDE, not a shared one. Yes, you can get monthly updates, but most
> organizations bother to update such data perhaps only once every 3-4
months.
> Why load 50,000+ zip codes over the network when you can process them
> locally?
>
> The bottom line is you need everything you can think of to reduce the
> network traffic - the records fetched from the file server. By the way,
20
> users is probably not too much for a well-designed client/server
application
> using the JET engine.
>
> Finally, you can go "whole hog" and do local table loading and editing to
> really squeeze the most out of a shared application, but it's a lot of
work.
> I know of one mdb-based application run by a major manufacturer support
> organization that handles 100+ concurrent users this way, but that is the
> rare exception rather than the norm.
>
> --
> John Viescas, author
> "Microsoft Office Access 2003 Inside Out"
> "Running Microsoft Access 2000"
> "SQL Queries for Mere Mortals"
> http://www.viescas.com/
> (Microsoft Access MVP since 1993)
> "T." <t@NOSPAM.ca> wrote in message news:_4M3c.15217$Up2.3591@pd7tw1no...
> > All of my forms serve both data entry and display purposes and
> > unfortunately I have never been in the practice of using the
WherCondition
> > parameter. This sounds like it may be the BIG clue I have been looking
> > for...what can I show you that would help get this figured out??
> >
> > T.
> >
> >
> > "John Viescas" <JohnV@nomail.please> wrote in message
> > news:OkPDqUuBEHA.3784@TK2MSFTNGP10.phx.gbl...
> > > Row sources of a couple of hundred rows aren't a big problem. In your
> > > example, make sure that the field ShawSystem is indexed.
> > >
> > > By filtering, I mean do you filter all data display forms when you
open
> > > them? If so, are the filter fields indexed? If not, why not? In any
> > > client/server application (even when the back end is SQL Server), you
> > should
> > > be using the WhereCondition parameter on any DoCmd.OpenForm. (If you
> tell
> > > me you're using macros, then you have an even bigger problem. <s>)
> > >
> > > When you use subforms, are your Link Master and Link Child fields
> indexed?
> > >
> > > --
> > > John Viescas, author
> > > "Microsoft Office Access 2003 Inside Out"
> > > "Running Microsoft Access 2000"
> > > "SQL Queries for Mere Mortals"
> > > http://www.viescas.com/
> > > (Microsoft Access MVP since 1993)
> > > "T." <t@NOSPAM.ca> wrote in message
> > news:3EI3c.763803$ts4.43149@pd7tw3no...
> > > > Hi John,
> > > >
> > > > Thanks for your reply. There could be up to 20 different users all
> > using
> > > > local front end MDE's. The only common file is the BE on the
server.
> > > >
> > > > Most of the forms are slow, but some are much slower than others. I
> do
> > > have
> > > > quite a few combo boxes that are loaded from queries on form open
but
> > they
> > > > would not have thousands of entries in them, maybe tens or hundreds
> > > though.
> > > >
> > > > Yes I believe that I do have the fields indexed but at this point
> > nothing
> > > is
> > > > really being filtered. Basically I am cheating it, users have to
> select
> > a
> > > > City and/or Community before they can save any of the records so
what
> I
> > am
> > > > doing is setting the RowSource equal to a qry that takes all the
> cities
> > > > where the office ID equals the login stored in a global variable....
> > > >
> > > > "SELECT [tblCity].[CityName] FROM tblCity WHERE
> [tblCity].[ShawSystem]=
> > '"
> > > &
> > > > UserID & "';"
> > > >
> > > > That way all the user can see and do is relevant to the office they
> are
> > > > logged into.
> > > >
> > > > Since my original post I have been playing with APPEND queries and
DEL
> > > > queries and have somthing that is working...sort of...but my concern
> is
> > > that
> > > > if I use an append query and someone from each office is logged in
> then
> > > the
> > > > temp table will be as large as the main one.
> > > >
> > > > Thanks again and I hope this helps clarify...
> > > >
> > > > T.
> > > >
> > > >
> > > > "John Viescas" <JohnV@nomail.please> wrote in message
> > > > news:ezvLfWsBEHA.2404@TK2MSFTNGP11.phx.gbl...
> > > > > You don't say how many concurrent users or how large your shared
> > "data"
> > > > file
> > > > > is. Also, does each user have their own copy of the "frontend" on
> > their
> > > > > machine? (It's not a good idea for all users to share one front
end
> > on
> > > a
> > > > > server.)
> > > > >
> > > > > It sounds like you're making an attempt to filter the records to
> only
> > > > those
> > > > > applicable to the user's office. Do you have an index on the
field
> or
> > > > > fields that provide the filtering values?
> > > > >
> > > > > Are *all* forms opening slowly, or just certain ones? Are you
using
> > > combo
> > > > > or list boxes with thousands of rows?
> > > > >
> > > > > There are lots of avenues you can explore before you go to the
> trouble
> > > of
> > > > > loading local tables and writing the code to attempt to write back
> > local
> > > > > updates to the shared data.
> > > > >
> > > > > --
> > > > > John Viescas, author
> > > > > "Microsoft Office Access 2003 Inside Out"
> > > > > "Running Microsoft Access 2000"
> > > > > "SQL Queries for Mere Mortals"
> > > > > http://www.viescas.com/
> > > > > (Microsoft Access MVP since 1993)
> > > > > "T." <t@NOSPAM.ca> wrote in message
> > news:ypH3c.14325$Up2.785@pd7tw1no...
> > > > > > Hi every one, I hope this is the right place to post this. Here
> is
> > > the
> > > > > > situation...I had originally developed a system that tracks all
> new
> > > > homes
> > > > > > being constructed in a city. I store Builder information,
Address
> > > > > > information and lots of other things. Everything ran very well
> with
> > a
> > > > > split
> > > > > > backend and frontend. Now all the other offices in my company
are
> > > using
> > > > > the
> > > > > > same system and we have one central backend for everyone that is
> on
> > > the
> > > > > > network. The problem that I am having is that because the
amount
> of
> > > > data
> > > > > > that is in all the tables now is so large it is taking FOREVER
to
> > open
> > > > up
> > > > > > any of the forms bound to a table.
> > > > > >
> > > > > > I have a login in process that stores the username in a global
> > > variable
> > > > > and
> > > > > > then I use that to derive information in the forms so that each
> > office
> > > > can
> > > > > > only see their data. I know that I am only fudging this and
that
> > all
> > > > the
> > > > > > data has to pulled from the table this way. What I think that I
> need
> > > to
> > > > do
> > > > > > is have a Make Table query get all the information out of the
main
> > > > tables
> > > > > > and run the forms off of them. Then use an Update Query?? to
save
> > any
> > > > > > changes made back in the main table and then when the system
> closes
> > > > clean
> > > > > > out all the temp tables.
> > > > > >
> > > > > > I have never worked on something like this before so I am having
a
>
> > few
> > > > > > problems and as there could be any number of people logged into
> the
> > BE
> > > I
> > > > > > don't know how the if the temp tables would work properly.
> > > > > >
> > > > > > Can anyone give me some direction on this??
> > > > > >
> > > > > > Many TIA,
> > > > > >
> > > > > > T.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>