Access ODBC

[andybrst]

I have created linked tables to files in our mainframe software, however, I want to automatically restrict the number of records in the table. Can you filter the number of records that Access calls? AS400 files appox 1.5M records and there are 3 files required. It can take upto 30 mins to produce one query report!!

 

[Corticus]

Hi andybrst,

By what criteria would you like to restrict the records? You can use the 'top' criteria to get the top xxx records like:

SELECT top10000 from * from Tbl1
WHERE .....

Of course, this is still another query that you're running on 1.5 mil records. Perhaps the query is the issue, I would take a look at that as well.

Post back if you need more...

HTH,

 

[andybrst]

The core files I'm linking into are very messy. File #1 is a customer file, file #2 is a customer order file, file #3 is a customer order line file. The aplication I want to run has a query over those three files. The key field in #1 to #2 is the customer reference, #2 to #3 is the order number. I want to just query on records within a certain date range for a certain customer reference. I thought if you could limit the records you call on in the ODBC, the query would run faster. The ODBC is Client Access into AS400.

 

[Corticus]

I guess I still don't understand,

By what do you want to limit the records?

To limit the records, you would run a query, but you already have that. The only thing I can think of that might help you would be to first do a query on the AS400 by just the client name, and then do the rest of your query on that query. Also, make sure your records are indexed.

HTH,

 

[gator]

Did you create a where statement that says where order_number="order number here" and client_reference="client reference here". There doesn't seem like there would be another way to make this faster. Such are the perils of a 1.5 Million record database.

-gator