Question on ODBC and SQL Differences (Access and Oracle)

[MFriend]

Hi Folks:

I work at a research facility that uses Oracle (version 11) for the storage of our data. I have been trying to learn more about Access 2010. It seems (so far) much easier to use Access 2010 to design client 'frontends', than the old version of Oracle Forms we have been using.



I have two questions I'm hoping someone will know:

1. If I link an Access front end to the Oracle database using ODBC, then package the front end as an application (with install program, etc. as output by access 2010), will the app when installed maintain the link to the Oracle database? (or will I have to install the ODBC Oracle driver and relink the tables afterwards?).

2. For my queries, am I able to use Oracle's version of SQL since the SQL is being passed to the Oracle database for processing, or do I have to use Access's ANSI-92?

Thanks for any help or info.

Matthew

[NTC]

Access performs its sql in the FE - which means you must write queries per Access specs....as one generally would not turn to Access simply for form development. If it is a hard core Oracle environment there are several Oracle oriented development/mining products available and am not sure Access is really the best approach depending on your need.

Access has integrated ODBC connectivity - and you'll simply have to follow those instructions and see what happens. There are alot of flavors of Oracle so one can't be sure.

in the most generic terms a table is a table is a table; however one must give consideration of the source (Oracle) table size - in particular its width. Access table width use to be 2k bytes - am not sure if that has increased in recent versions. also Oracle version whatever can have field definitions unfamiliar to Access. Also these tables can be monster big and hanging Access onto it may be a stretch. Your Oracle people probably can automate a small table record set for you to connect to - or possibly an export into a neutral format such as csv.

[MFriend]

Hi NTC:

Thanks for responding. So if I understand you correctly, when using ODBC, the SQL is not passed to Oracle to run the SQL, but Access on the front end does it? I assume this means then that all the data from a table(s) would have to be transferred to the client machine in order for Access to run SQL against the data. This would be really slow (especially since some of our tables have several million rows).

I guess I had assume (wrongly) that the SQL would be run by Oracle and just the results transferred across the network back to the client machine...

Matt

[NTC]

The essential difference of Access vs sqlserver, oracle, etc - is this very aspect that Access is a desktop solution and is performing the sql in the FE. And yes when one is into tables with millions of records Access is not the correct solution.

To non-tech people I use the analogy of the pickup truck (Access) vs the semi tractor 18 wheeler (Oracle, sqlserver, db2). If it is several tons of produce across country you need the 18 wheeler; but if it is several cartons across town you're better off with a pickup truck. It isn't about which technology is better, it is about which is appropriate for your task. Access is by far the best pickup truck solution in the industry. But since your question revolves around connecting to Oracle tables one has to be clear on things - - it is not the best mining tool for big Oracle dbs; on the other hand if your Oracle people can automate exporting smaller tables - - then Access is an excellent tool to use on those....and this is very common.

[MFriend]

Hi NTC:

Thank you for responding. After doing more research I believe ACCESS has the ability to 'access' large databases built in and will meet my needs.

I had two 'issues' with access: 1. the very large tables on the server/backend (some tables have almost 3 million records: almost 2 gigs in size) and the problem of having to transfer all the data across the network to perform data manipulation, and 2. Access having its own version of SQL (my coworkers are only used to using Oracles version of SQL).

The answer (unless I'm missing something) appears to be using "Pass-Through" which is built into Access. By using "Pass-Through" the SQL is NOT manipulated on the FE, but sent to the server (Oracle) for processing. Only the results are sent back. With Pass-Through the user has to use Oracles SQL not Access' SQL engine, so my coworkers would be able to understand my SQL code. I tried a couple of queries using pass-through and it seems to work great.

If anyone knows of a reason not to use "Pass-Through" or anything to watch out for I would appreciate knowing.

Thanks
Matthew

[NTC]

you are correct on this and I should have mentioned this configuration.

the issue is however that if Access is set up with passthru - then what's the point of using Access? for form design? plus if you create a query that is going to result in a file or attempt to look at a table too big for Access, it is still going to crash...

my primary point was that if one is entering into the world of mining against an Oracle/sqlserver/db2 'big' environment that Access is not a slam dunk - - while Access has some ability, it isn't necessarily the ideal mining tool (and sometimes a bottleneck). In some requirements it is obvious that Access is the right tool, but in this type requirement it is less certain..... If I was charged with comprehensive Oracle mining/reporting I would look at products specifically designed for that function (they are pricey).

but yes, you can use Access in passthru mode to query an Oracle db, and as mentioned in prior post it is very common to use Access in normal mode on tables/record sets from 'big' dbs that are clearly/intentionally sized to be within Access' spec.