Re: ODBC Call structure

Ryan

I'll jump back in for a re-hash.

When an Access database (i.e., tables) has slow queries, the first place I
look is at the indexing on the tables.

If the selection criteria, sorting columns, and join columns are not
indexed, Access will have to (internally) return all the rows to work with
them locally.

If the selection criteria are not applied until the very last step, Access
has to (internally) return all the rows to work with them.

If the query involves functions available in Access but NOT available in the
underlying data storage, Access has to (internally) return all the rows to
work with them.

Each of these provide potential speed ups. The first approach is to ensure
appropriate indexing. The second suggests putting selection early. The
third suggests putting off using internal Access functions until after all
other selection narrowing is done (if possible).

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ryan" <Ryan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:336731D3-389E-4216-B8C9-60ED400FEDB5@xxxxxxxxxxxxxxxx
I would love to run queries on the linked tables, but they just take way to
long. I have very complex expressions that need to be built into the
queries
and I cant even get sum and count totals to run smoothly. If someone
could
tell me way to make the linked table queries run without locking up access
and in a timely manner I would be forever indebted.

"Jeff Boyce" wrote:

Ryan

I confess to being a bit of a data bigot. I'd look for other solutions
than
replicating all the data in Access. But if the only way to get
acceptable
performance is to constantly re-synchronize/reload local tables from a
remote source, so be it!

(Hopefully other newsgroup readers have a more elegant solution!)

Regards & Good Luck!

Jeff Boyce
Microsoft Office/Access MVP

"Ryan" <Ryan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F83F8942-8FB3-41C8-ADDA-D37574C952B4@xxxxxxxxxxxxxxxx
I have rebuilt every query and started over at least 10 times trying to
get
this to work. I have changed strategies. I ran a create table queary
on
all
6 queries, and now I'm working on update and append queries to keep the
tables in access current. When I run the queries in access it never
takes
longer than a few seconds. This seems like the best plan of attack
because
it will run faster and keep the connection to the AS400 open only long
enough
to run the update and append queries. Your suggestions on this
strategy.?.

"Jeff Boyce" wrote:

Ryan

Rarely, but it's happened, I've had a query go "sour". Something is
wrong
somewhere, but I can't see it and Access doesn't fix it when I run
Compact &
Repair.

When a query just isn't getting the job done, I've been know to throw
it
away and start over with a brand new, never been touched query, built
up
one
small step at a time.

Can you create a new query that does what it should?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ryan" <Ryan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3FFDED23-2987-4EBB-91D2-3207307677B5@xxxxxxxxxxxxxxxx
Here is something interesting. I tried to run a find unmatched
query,
and
the field that is my PK ([MBRFAM#]) wasnt available. I deleted
every
field
in my query except that field and when I tried to build the find
unmatched
query again it told me "There are no fields available in this table
or
query
that are valid for a join. Select a talbe or query that contains at
least
one field that does not have the memo or OLE data type. The weird
thing
is
that the field is a numeric field. I have already tried the raw
data
queries
and the total and sum queries on top of them and had the same long
run
time
results. Please advise.

"Jeff Boyce" wrote:

Ryan

One approach might be to create all the queries that return the
"raw"
records, then create new queries based on the first set that do the
totalling. It may be that trying to return rows AND total is
causing
Access
to download all the rows before totalling selected rows.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Ryan" <Ryan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D7D35D13-694E-4A15-A7B7-3E40548664C4@xxxxxxxxxxxxxxxx
I have narrowed the problem down to including totals in the query,
not
joins.
If I run any of my queries, joined or not, and do not include
totals,
then
the query runs very quickly, but as soon as I add totals it takes
forever.
I
have 10 very complex expressions to write so I have to have
totals.
Any
suggestions?

"Jeff Boyce" wrote:

Ryan

If your multi-table query includes any Access-only functions, it
will
run
more slowly.

If your tables don't include indexing for your join fields, or
for
your
selection criteria fields, or for you sort-by fields, the query
will
run
more slowly.

It sounds like Access isn't able to optimize the query and so
must
resort
to
downloading all of the tables' rows to your PC before completing
the
query.

Can you create "views" in your AS/400 data set? If so, do the
joins
and
as
many of the selection and sorting criteria there as you can?
Then
link
to
the view.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Ryan" <Ryan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:64D31281-2B61-490C-8C31-CFFF2E40ADB4@xxxxxxxxxxxxxxxx
I am using Access 2003 through ODBC to link to an IBM AS/400.
If
I
query
only one table it takes about 15-20 seconds, but any joined or
sum
queries
can take up to 10 minutes and cause Access to temporarily not
respond.
Is
there a trick to making join or sum queries via ODBC or will
it
always
take a
long time to run?














.

Relevant Pages

  • Re: Same Parameters in all sub reports
    ... something wrong between the form and the query. ... It didn't for my regular queries. ... "Jeff Boyce" wrote: ... Build a query that will be used to provide data to your report. ...
    (microsoft.public.access.reports)
  • Re: Compare two tables and write results to third table
    ... users and understand tables and records and queries and code. ... combine an Append query and an Update query as one. ... "Jeff Boyce" wrote: ... The new list can have folks who moved, it can have new folks and where they ...
    (microsoft.public.access.queries)
  • Re: ODBC Call structure
    ... queries, and now I'm working on update and append queries to keep the ... "Jeff Boyce" wrote: ... Rarely, but it's happened, I've had a query go "sour". ... but as soon as I add totals it takes ...
    (microsoft.public.access.externaldata)
  • RE: Dynamically referencing a recordset?
    ... through that recordset to set the query parameters in VBA. ... to be the one who had to modify 1440 queries if there is a change in the ... different table schema, then you need 3, one for each record type. ... This is a database that tracks the production on records in another ...
    (microsoft.public.access.modulesdaovba)
  • Re: Was: what does "serialization" mean?
    ... > the specific queries up front. ... that the NEXT time the query is run the query is fast, ... data base, don't know squat" when in fact the Donald clone doesn't ... >> WHATEVER would probably be a separate query to summarize total sales. ...
    (comp.programming)