Re: Need some query design help....

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Relevant Pages

  • Re: YO!! Joe Sparebuttons...
    ... John H ... customers end up with something which doesn't serve their ... This is basic sales training, by the way, which at its best does NOT ... car dealerships, consumer electronics and appliances. ...
    (rec.boats)
  • Re: YO!! Joe Sparebuttons...
    ... John H ... customers end up with something which doesn't serve ... Your otherwise brilliant woman was stupid, ... car dealerships, consumer electronics and appliances. ...
    (rec.boats)
  • Re: YO!! Joe Sparebuttons...
    ... John H ... customers end up with something which doesn't serve their ... Your otherwise brilliant woman was stupid, ... car dealerships, consumer electronics and appliances. ...
    (rec.boats)
  • Re: Update Query
    ... Here is my query: ... John W. Vinson ... Customer and Payforward ... Customers has many fields with MemID as the unique index field ...
    (microsoft.public.access.queries)
  • Re: Virtual mail backscatter
    ... // outbound dnsbl filtering to catch our own ... // customers that end up on the sbl ... dnsbl_list local sbl dul; ... // inbound content filtering to block spam for our customers ...
    (comp.mail.sendmail)