Linking Oracle to MS Access for large databases

[pgrau2342]

Trying to link MS Access on local PC to very large Oracle database (100's of millions of records), and I am worried it will "blow up" local PC and Oracle server using standard ODBC drivers. Any recommendations where Oracle server could do the queries and Access just does reporting? Thanks

[magicwand]

Any recommendations where Oracle server could do the queries and Access just does reporting?

The strongest possible recommendation: ALWAYS !

[MMcCarthy]

You will need to create views on the oracle server corresponding to the queries. Then link those views as tables in the Access database.

Alternatively, you could use PassThru SQL queries in Access. These are queries in Access that directly query the odbc linked oracle database. The queries must be written in Oracle SQL.

[Jerry Winston]

Careful when working with MS Access and production level database systems like Oracle, MS SQL, and MySQL. Access has file size limitations (4gb) and does not have a robust server engine to power through data. Attaching an Access front end to an Oracle back-end is kinda like attaching a 2 cylinder engine to a freight load.

I have to ask why would you need to attach Access to your Oracle db? Reporting? Data entry forms? What ever your requirement, take this warning with you as you develop: Any database configuration that includes MS Access is NOT production quality. Microsoft continually adds/drops major functionality from it's office product. Anything you develop in Access 2007 will not convert cleanly to Access 2010 or 2012 ect. Access lacks the advanced data management and multi-user support essential for deploying a production-level database.

I have spent YEARS rescuing inconsistent, orphaned, and black-boxed data trapped in Access databases. I've seen companies become married to multiple versions of Access simply because the key functionality on which a solution was built was not continued in the next version. I'm talking Access 1.0 - 2007. Although it's kept me gainfully employed, I'd rather fine tune well-built databases than keep rebuild Access-based data solutions.

Please consider developing your front-end in an established programming language as a desktop application or a web UI. Think about using industry standard reporting tools like SSRS,Crystal Reports, or Business Objects. If it's worth doing, it's worth doing right.

[MMcCarthy]

@Jerry

Are you trying to talk me out of a job :D

I understand the points you are making but the 4gb size limitation on Access is not a problem as long as there are no embedded queries in the Access application. Using linked views, Pass-Thru queries and recordsets allows for the stable management of large datasets from backend databases like Oracle.

I agree a lot of companies don't follow these rules but if they are followed then Access can be successfully used as a frontend.

The main reason for using Access is you can simply design a user friendly interface.

Now I'll step down off my soap box lol!

Mary

[Jerry Winston]

@Mary

I haven't had my "E" for a month and I've already stuck my neck out with the admins! :)

I was speaking specifically about home-grown solutions that improperly implement Access and some of the potential "gotchas". Obviously, I haven't had to fix a well-designed Access solution - because they're all still working!

Jerry

[MMcCarthy]

@Jerry

Nice Save :D