Need some query design help....

[T.]

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.

 

[T.]

EDIT - Also if anyone can give me suggestions on turning off the
verification prompts in DEL, MAKE and UPDATE tables that would be great.

Thanks,

T.

 

[John Viescas]

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)

 

[John Viescas]

If you run the queries from code using the Execute method, you don't need to
know how to do this. But the command is Setwarnings False.

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

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]

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)

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.

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)
using
the variable
and

to

do

 

[T.]

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.

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)

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.


"data"
file

on

a open
up office
can all
the to

BE

I

 

[John Viescas]

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)

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.

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)
do
have nothing

select

a

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]=

'" is
that then
the on trouble
of is
the

with

a on
the need
to

few

the

BE

 

[T.]

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.

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)

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.


but
they select

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.


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 with

amount

of to
open that
all save
any

a

the

 

[John Viescas]

I cover it briefly in my Inside Out book, but the best "bible" on the topic
is "Microsoft Access Developers Guide to SQL Server."
http://www.amazon.com/exec/obidos/ASIN/0672319446/viescaconsulinc/ Don't be
put off by SQL Server in the title - the design constraints are the same for
any C/S application regardless of the back end.

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

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.

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)

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.


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


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)
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 into
the