I have a linked table with a huge amount of data.  I created a query where I 
would type in the criteria I wanted to pull.  This took approx 30 seconds 
which for my purposes I was okay with.  However, I am now trying to make this 
process an automated process, where I can push a button, go to lunch and I 
have created files for all the customers I need.  Here is the challenge.  
When I create the query and type the criteria in, it runs fine.  When I try 
to put a value (via code) on a form, reference that value on the form in the 
query, the process does not complete, just hangs.  So I tried using code 
(creating a new qdf) to make a new query with the appropriate value as my 
criteria thinking this would run as quickly as the original query, wrong!  So 
my question is, how can I get this mountain of data in a linked Oracle table 
to run and not hang.  Not sure if this is a ODBC issue or a query, issue, 
seems like a little of both!
Thanks Much!

lbernarde wrote:
> I have a linked table with a huge amount of data.  I created a query
> where I would type in the criteria I wanted to pull.  This took
> approx 30 seconds which for my purposes I was okay with.  However, I
> am now trying to make this process an automated process, where I can
> push a button, go to lunch and I have created files for all the
> customers I need.  Here is the challenge. When I create the query and
> type the criteria in, it runs fine.  When I try to put a value (via
> code) on a form, reference that value on the form in the query, the
> process does not complete, just hangs.  So I tried using code
> (creating a new qdf) to make a new query with the appropriate value
> as my criteria thinking this would run as quickly as the original
> query, wrong!  So my question is, how can I get this mountain of data
> in a linked Oracle table to run and not hang.  Not sure if this is a
> ODBC issue or a query, issue, seems like a little of both!
> Thanks Much!

How exactly did you use the query that "took approximately 30 seconds"?  If all 
you did was open it in datasheet view then you were only seeing the results of a 
few pages worth of data.  If your current process needs to process the FULL 
query then that is why it is taking longer.

How much data are we talking about?  Are there any joins in the query?  If there 
are you should use a view or stored procedure on the server as joins against 
linked ODBC tables can be inefficient.

-- 
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com 

It actually was a make table.  It returned approximately 30,000 records.  I'm 
not sure on the total record size of the table.  I'm thinking 500,000 plus, 
opening right now.  Right now I do not have any joins.  I'm trying to get the 
smaller dataset to work with then add the necessary joins and data 
conversion.  Is there a way I can create a snapshot of the linked table and 
get my smaller dataset from that to work with?  Haven't done that before is 
it possible?

"Rick Brandt" wrote:

> lbernarde wrote:
> > I have a linked table with a huge amount of data.  I created a query
> > where I would type in the criteria I wanted to pull.  This took
> > approx 30 seconds which for my purposes I was okay with.  However, I
> > am now trying to make this process an automated process, where I can
> > push a button, go to lunch and I have created files for all the
> > customers I need.  Here is the challenge. When I create the query and
> > type the criteria in, it runs fine.  When I try to put a value (via
> > code) on a form, reference that value on the form in the query, the
> > process does not complete, just hangs.  So I tried using code
> > (creating a new qdf) to make a new query with the appropriate value
> > as my criteria thinking this would run as quickly as the original
> > query, wrong!  So my question is, how can I get this mountain of data
> > in a linked Oracle table to run and not hang.  Not sure if this is a
> > ODBC issue or a query, issue, seems like a little of both!
> > Thanks Much!
> 
> How exactly did you use the query that "took approximately 30 seconds"?  If all 
> you did was open it in datasheet view then you were only seeing the results of a 
> few pages worth of data.  If your current process needs to process the FULL 
> query then that is why it is taking longer.
> 
> How much data are we talking about?  Are there any joins in the query?  If there 
> are you should use a view or stored procedure on the server as joins against 
> linked ODBC tables can be inefficient.
> 
> -- 
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt   at   Hunter   dot   com 
> 
> 
>