Access Reporting w/Oracle back end

[Denda]

Hi all -

First off let me say, 'I'm not an Access Programmer', but I'm trying to troubleshoot some issues that I'm getting conflicting statements about. I'm the sys admin for our company. We have an access database sitting locally on our remote plants network server. This access database runs a sql query to extract data from our Oracle database at our corporate site ( my site ) for reporting of inventory.

I've been reading about when Access 2000 connects to a database it actually pulls the whole table for the data down to the desktop & then filters for the records locally. Is this true? Our Access programmer states that it is not true, that Access pulls individual specified records from the table only, not the whole table.

Well, one month ago our remote site started complaining about a couple specific reports that are taking up to 3 times longer to run, what used to take 45 seconds, now takes 3 minutes.

I became involved because the programmer thought it was a network issue. This application (Access) is the only app that is having issues over the WAN & it's only with a few specific reports. Even though I did not think it was a network issue, I did my duty & checked all local pc's at the remote site for problems, checked all routers & switches for throughput issues & nothing is showing any problems.

Programmer states nothing has changed on his end with the program.

Is there a utility that I can run that will tell me how the records are being pulled? Whether the individual records are selectively pulled or if the whole table is being pulled or any type of data extraction troubleshooting?

Thank you in advance.

 

[lespaul]

Here's a recent thread that discuss Access over a network, perhaps it will help you find your problem:

Thread700-866385

HTH

leslie

 

[Denda]

Already looked at that. Programmer states it's not the case... That's why I'm posting exact details of our issue. Thanks anyways.

 

[cmmrfrds]

Since it is a couple of specific reports, can you describe the data source for those reports or at least 1 report. Is the Report bound to a table, query, stored procedure, View, or SQL Statement? How is the data source connected? Through a linked oracle source, such as, a view, table, or stored procedure. An ADO connection? An ODBC connection? A pass-through query?

 

[Denda]

Data Source is ODBC (system DSN), the report is the result of an Oracle query. I've asked my Access programmer to please give me the query so I can research what it is doing & asking for, but I haven't rec'd it yet. I'll post here when I get it.

 

[cmmrfrds]

How do you know it is an Oracle query as opposed to an Access query against an Oracle linked table? Please describe.

 

[Denda]

I just asked him that & he came back with now it's an access query against an Oracle linked table. Sorry about the misinformation I gave here, but that was how it was explained to me previously. Thanks for your help

 

[cmmrfrds]

The Oracle linked table approach is going to be very inefficient. There needs to be lots of data sent back to the desktop for Access to determine which data to select in the query.

If you don't need to bind the oracle data to an Access Form for update purposes then, it is easy to get by without linking the tables. There are multiple options to send the query directly to Oracle for processing and only return the selected records. Either a pass-thur query or ADO recordset will work. Does the app need to update the Oracle data?

 

[Denda]

thank you. I'm trying to tread lightly since this is questioning a programmers program that I know nothing about. However, I forwarded your response to him & he states he is already working on something to pinpoin the issue. Thank you again for all your help. I'll keep you updated.

 

[Denda]

oh yeah, to answer your question.. No, it doesn't need to update the Oracle data. It's just reporting what is there. Thanks again