Oracle in the back, Access in the front?

Question

I "inherited" an Access 2003 project. Now they've begun upgrading us to 2007. I'm low man on the totem pole (and rightly so), so I don't have access - ha, no pun intended - to the Big Mama Oracle db, only the dumps that have been saved as tables (and built into a multitude of queries) in Access.

So, some very basic questions in order to get my bearings.

1. I learned from this discussion that, owing to the complexity of the reports, I should be thinking in terms of Stored Procedures. OK, I like that idea. It's good programming.

2. Access 07 supports (apparently), something like stored procedures (doesn't it?). However, I've read scary things about it, and much of the rest of the department has yet to upgrade from '03. If I do my work in '07, their '03's will not know what to do with my beautiful Stored Procedures, right? FURTHERMORE, if it turns out that '07 is really NOT the right choice for this project (for whatever reason -- who knows, it's new to this operation), then all the time invested is instantly obsolesced.

3. Since Big Mama IS an Oracle dB, clearly that's got to be stable. So, why don't I just wrap my head around SP's in Oracle? It seems like it would result in the most robust application for all: I'm given to understand that I can teach both Access '03 and '07 how to call those Oracle SP's. Plus, my coding will be lower level and closer to the source, which promotes stability and efficiency.

Can I actually create an Oracle-centric SP in Access '07 (or '03). I kinda doubt it.

Answer 1

If you're stuck using Access backed by an Oracle database, I reckon a reasonable path to follow would be to offload as much work to Oracle as possible.

That means, get Oracle to do all the heavy lifting with procedures and functions (preferably encapsulated in packages), and views. Then, use JDBC to allow Access to just query and present the results.

This means learning SQL and PL/SQL, but I think it's worth it :)