MS Access ODBC live link to Oracle 1

[JanvRooyen]

{We implemented Manugistics (a demand management & forecasting tool), running on an Oracle database. The forecast performance reports extracted via Manugistics takes a long time to run. The result a &quot;flat&quot; file we have to import into MS Access to massage the data into the sales reports.}<br>
<br>
An alternative could be to bolt MS Access directly onto Oracle and use the ODBC live link.<br>
<br>
1) How quick (in terms of accessing speed) is this link?<br>
2) Does the MS Access Jet Engine come into play if we access the Oracle tables, or does Oracle employ its own search engine?<br>
3) If we want to aggregate the detail data in the tables (example: per &quot;sales region&quot, but this &quot;sales region&quot; field is not a KEY field, will this influence the speed of executing a query in MS Access? How much will this influence be?<br>
<br>
Jan

 

[JimHorton]

Speed, Jet, Keys...Below should give you answers to these three.<br>
That all depends. With Access/Oracle, I have created reports that run over multi-table queries with many millions of records, that return the report set in seconds. However, this is when the Sorting/Grouping fields are indexed, as well as the criteria fields. If your DBA can allocate the space, then index profusely and you'll usually be ok. This can be either an Access query in the qbe grid, or Pass-Thru. The ODBC drivers can translate fairly well now, and a simple query in the Access grid will run like a pass thru. There are things that don't translate well, especially the more complex joins. Here, just base the report on a passthru, and make sure the join logic (you cant use JOIN keyword) is right. This is tricky.<br>
<br>
Also, it can be beneficial, especially if the report data is fairly static--ie the reports are running yesterday's stats, and don't need up to the second data---to create a temporary table on the Oracle box using a Passthru, creating a flat file (ala data warehousing), then running a straight Access query over that. You can then run different reports all day with impressive speed. <br>
<br>
As with any indexing, evaluate the balance between the slowdown in inserts/updates with the extra indexes. Same for aggregate data, indexing group by fields will help. Again, keep in mind that an index over, say, a character(50) field for 10 million records is half a Gig, and rebuilding this index takes time and processing power. This can rattle DBA's. <br>
--Jim