ODBC Call structure

[Ryan]

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?

 

[Jeff Boyce]

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]

I have done some additional testing since I left this post. Im not sure what
a view on the AS400 is so I have left a message with the group that handles
our AS400. I have also done some make table queries and when I join the
tables in access it runs very fast. Shouldnt I be able to do the same with
my AS400? I will wait to find out about the views, but even without any
sorting or indexing if I query more than one table the wait time is forever.
Making tables works well, but I dont think its good practice to make tables
every time I want to view my data. Other than the views you spoke about, is
there any thing else I can do to troubleshoot this issue? Thanks for your
help so far Jeff.

 

[Ryan]

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]

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]

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]

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

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.

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]

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

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

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.

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

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?

:

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


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?

 

[Jeff Boyce]

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

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

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

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.

:

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

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?

:

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


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?

 

[Ryan]

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.

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

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

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

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.

:

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

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?

:

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


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?

 

[Jeff Boyce]

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

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.

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

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

:

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

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.

:

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

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?

:

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


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?

 

[Ryan]

Thanks for your time and help. I did decide to run make table queries. This
seems to be the fastest way to my data because it only takes about a minute
and a half to run the make table queries. This also means that I'm only
using resources on the AS400 and the network for that length of time. Your
advice was very much apprieciated.!.

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

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.

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

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

:

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

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.

:

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

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?

:

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


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?