Re: Oracle DB via Access - Still C/S?

nobody_at_nowhere.com (Jason W. Paul) wrote in <3e4d77e2$0$27927$a0465688_at_nnrp.fuse.net>:

>
>"Steve Jorgensen" <nospam_at_nospam.nospam> wrote in message
>news:rrqq4vkntcimrsuidvija6v3mcdjd0dd96_at_4ax.com...
>> On Fri, 14 Feb 2003 18:09:02 -0330, Tim Marshall <Sabot_at_Spam.On>
>> wrote:
>>
>> >Crossposted to comp.databases.ms-access and
>> >comp.databases.oracle.tools
>> >
>> >I do plenty of apps with Access as the application and Oracle
>> >as the database using ODBC and Access SQL against linked Oracle
>> >tables as well as Access pass through queries (using Oracle SQL
>> >against the ODBC DSN).
>> >
>> >Is such an arrangement still considered to be a client server
>> >application? This question may belie my uncertainty about the
>> >exact definitions of a client server application (what I
>> >believe most of the Omnis/Oracle, Powerbuilder/Oracle I am
>> >familiar with are) versus a file server app.
>> >
>> >Thanks in advance for any comments on this subject. I
>> >apologise for the cross post, but felt the question was
>> >relevant to both groups.
>>
>> The conventional definition of C/S with regard to database
>> applications refers to the fact that there is a database server
>> process running on the request of a client application. Oracle
>> vertainly is a database server! Therefore, using Access as a
>> ffront-end to Oracle is unquestionably C/S.
>
>I disagree. In a lot (lot more than I care to accept) of
>situations, if you're using linked tables, the brain-dead Jet
>engine decides that the server shouldn't do any processing and
>will drag *huge* amounts of data, kicking and screaming (to the
>chagrin of your network folks and DBA) across the wire --
>essentially negating the entire C/S model.

Irrelevant. There is still a single process running on a server that is answering all the requests for data from client workstations, regardless of how inefficiently those requests may be structured.

Of course, you exaggerate Jet's inefficiency: it actually is smart enough to pre-parse what it sends to do a surprising number of things (such as joins) server-side.

And if Jet isn't smart enough for the needs of your application, you use passthrough queries, or, better yet, server-side views and stored procedures.

Failure to do those things does not magically transform the architecture from C/S to a file server architecture. It's just bad design.

>If all data access is done via pass-thrus, then yeah, I'd say it's
>C/S.

It's C/S regardless, as long as the data retrieval is all done by a process running on a remote server, and not through mounting the remote file system locally.

>If you've got a lot of linkage (linked tables) going on then,
>maybe it's C/S maybe not -- depending on what Jet decides to do
>with the queries

You're wrong: it's C/S because the server database is doing all the interaction with the data store.

>Just wait 'til you're hitting a big table with a couple of 100s of
>millions of records and Jet decides to go flakey. It will.
>Randomly. Most times it will be very smart about data access but
>sometimes it will change the query plan and do something stupid --
>totally brain dead. If you've got millions of rows, and it decides
>to get stupid on ya, you're screwed.

I think the objections to Jet in C/S scenarios that Microsoft must have been responding to with the "Jetless" ADP demonstrates how poorly many people have learned how to properly use Access in a C/S environment. There are plenty of tools for building an efficient Access application with linked ODBC tables that don't make for inefficiency.

Even *I* know about these approaches, though I don't build C/S applications. Discussion of C/S design issues is in every advanced book on Access application building that I own.

>Because of this, you'll want most (if not all) of your data access
>to be via pass-thrus, and even those should not contain any "raw"
>SQL, but should be calls into pre-compiled stored procs on the
>DB server. In the "old days" I switched to ODBCDirect for this
>kind of stuff, guaranteeing that Jet would be bypassed.

Well, the smartest way to do things is to build views and stored procedures server-side, not passthroughs.

>I actually skip pass-thru querydef objects completely and now
>do all data access via ADO in code.

That's certainly another way to be efficient with Access against a server database engine. But it was not available as a usable option until June 1999, and only then using Access 2000.

>Don't get me wrong, I absolutely love Access -- especially it's
>report writer -- it's just that the native Access database engine
>(Jet) is prone to go flakey on you and make you look bad. If
>you take appropriate precautions, it's the greatest thing since
>sliced bread!

You simply don't know *how* to use Jet efficiently.

You and the vast majority of the rest of the world, I guess.

-- 
David W. Fenton                        http://www.bway.net/~dfenton
dfenton at bway dot net                http://www.bway.net/~dfassoc