Oracle vs SQL Server as a back end for Access?

[dreadnought8]

I've worked with mdbs, and with SQL Server to a lesser extent, with
Access as a front end, on commercial-strength systems for quite a
while, starting with A97.
The last 8 months or so, I've been working on Access 2000/2002 with
pass through queries and linked tables to an Oracle 10g backend
(ODBC).
Access seems (still) to beat everything else hands down as a front
end/ prototyping tool for WIndows based systems. With my type of
small to medium customer. And for parts of large corporations too, if
you can get past the poor reputation Access inherits from poor
programmers.
To my mind, Oracle seems to be a genuine alternative to SQL Server/
TSQL as a backend.
One of my main gripes with SQL Server was error handling within TSQL
procedures. Maybe they've got their act together with SQL Server 2005?
but the error handling in SQL Server 2000 seemed a minefield to me.
Exception handling in 10g seems much more logical.
I like PL/SQL better than TSQL.
10g SQL has some very powerful features including INSERT ALL, which
has saved me a lot of time in my current job. Maybe SQL Server has
too, I haven't checked.
Recently I loaded Oracle Express (free) and played with it.
I'm not sure what disadvantages there are over the full licence, but
it looks very attractive and has a nice UI, similat to enterprise
manager.
And I really like SQLPLUS - the Oracle command line interface (showing
my age here).
I realise you get polarised views with a topic like this - the more
you work with a tool, the better you become at using it - the more you
defend it.
Comments anyone?

[Tom van Stiphout]

On 17 Apr 2007 04:31:15 -0700, dr**********@ho tmail.com wrote:

If you think SQL Server and Oracle are the same, why don't you answer
questions like "Accessing Time Stamp Field Data in Oracle 10 with
Access 2003" posted just after yours, and similars that crop up here
on a regular basis.

Here is an article about incompatibiliti es:
http://support.microsoft.com/kb/244661/

T-SQL now has try/catch support.

-Tom.

>I've worked with mdbs, and with SQL Server to a lesser extent, with
Access as a front end, on commercial-strength systems for quite a
while, starting with A97.
The last 8 months or so, I've been working on Access 2000/2002 with
pass through queries and linked tables to an Oracle 10g backend
(ODBC).
Access seems (still) to beat everything else hands down as a front
end/ prototyping tool for WIndows based systems. With my type of
small to medium customer. And for parts of large corporations too, if
you can get past the poor reputation Access inherits from poor
programmers.
To my mind, Oracle seems to be a genuine alternative to SQL Server/
TSQL as a backend.
One of my main gripes with SQL Server was error handling within TSQL
procedures. Maybe they've got their act together with SQL Server 2005?
but the error handling in SQL Server 2000 seemed a minefield to me.
Exception handling in 10g seems much more logical.
I like PL/SQL better than TSQL.
10g SQL has some very powerful features including INSERT ALL, which
has saved me a lot of time in my current job. Maybe SQL Server has
too, I haven't checked.
Recently I loaded Oracle Express (free) and played with it.
I'm not sure what disadvantages there are over the full licence, but
it looks very attractive and has a nice UI, similat to enterprise
manager.
And I really like SQLPLUS - the Oracle command line interface (showing
my age here).
I realise you get polarised views with a topic like this - the more
you work with a tool, the better you become at using it - the more you
defend it.
Comments anyone?

[Hooker DBA [MSFT]]

Oracle is allright

but it has nothing similiar to ADP

ADP rocks; MDB is lame

ADP against SQL Server runs circles around your MDB crap


On Apr 17, 4:31 am, dreadnoug...@ho tmail.com wrote:

I've worked with mdbs, and with SQL Server to a lesser extent, with
Access as a front end, on commercial-strength systems for quite a
while, starting with A97.
The last 8 months or so, I've been working on Access 2000/2002 with
pass through queries and linked tables to an Oracle 10g backend
(ODBC).
Access seems (still) to beat everything else hands down as a front
end/ prototyping tool for WIndows based systems. With my type of
small to medium customer. And for parts of large corporations too, if
you can get past the poor reputation Access inherits from poor
programmers.
To my mind, Oracle seems to be a genuine alternative to SQL Server/
TSQL as a backend.
One of my main gripes with SQL Server was error handling within TSQL
procedures. Maybe they've got their act together with SQL Server 2005?
but the error handling in SQL Server 2000 seemed a minefield to me.
Exception handling in 10g seems much more logical.
I like PL/SQL better than TSQL.
10g SQL has some very powerful features including INSERT ALL, which
has saved me a lot of time in my current job. Maybe SQL Server has
too, I haven't checked.
Recently I loaded Oracle Express (free) and played with it.
I'm not sure what disadvantages there are over the full licence, but
it looks very attractive and has a nice UI, similat to enterprise
manager.
And I really like SQLPLUS - the Oracle command line interface (showing
my age here).
I realise you get polarised views with a topic like this - the more
you work with a tool, the better you become at using it - the more you
defend it.
Comments anyone?

[GH]

I am not sure what you are specifically asking for comments on in your
post -- comparing back end database engines or replacing the front end
interface or even both. Obviously, the intelligent responses posted
thus far seem to support some confusion as to your position. If you
are asking about what database works best as a back end with an Access
front end, you can generally bet that Microsoft's own SQL Server will
be more compatible. However, if you are looking at other criteria,
such as performance; cost; and capability, you need to assess the back
end database products against your requirements. You cannot wholesale
say that Oracle 10g is better than SQL Server 2000 without determining
whether or not the total cost of ownership of 10g over SQL Server
doesn't ultimately make Oracle's product priced out of the market.
SQL Server 2000 and 2005 are still easier to learn for the average
developer than Oracle 10g, in spite of its new web interface. I have
been a huge proponent of Oracle for years to my customers, but I am
realistic enough to know that different database products work better
in different situations. Generally, when cost was a huge issue, SQL
Server came out on top. Changes in licensing structure for SQL Server
2005 and Oracle 10g certainly put the two products closer in initial
costs, but an Oracle DBA still costs significantly more than a SQL
Server DBA. SQL Server has also made significant strides toward
better security, a realm Oracle had a stronghold over for many years.
If security is critical, I would still recommend Oracle over SQL
Server 2000, but I am less inclined to say Oracle is so much better
than SQL Server 2005 that a significant difference in cost of
ownership is merited. Finally, performance is always a factor.
Generally, unless you are doing some serious data cube and complex
analytics, SQL Server will perform close enough to Oracle to make the
differences irrelevant as a point of contention. The bottom line is
that there really is no clear cut winner in the battle of server-based
database management systems; you have to determine your application
requirements then figure out which database provides the best value.

Just my opinion - GH
Oracle Certified Professional and Microsoft Certified Database
Administrator

[Rich P]

I have extensive experience Access ADP's and Sql Server as a back end.
There is nothing wrong with the error handling in Sql server (2000 and
up). There IS a problem, however. The problem is with Access. It can
only read about 20% of sql server error messages. This probably has to
do with the extensive subclassing of Access (the built-in features of
Access to make it appealing to programmer/non programmer users - capture
the widest market possible).

Since Access Usage is widespread, I have to stay in the know with all
the latest and greatest developments for Access. As for backends/ front
ends --

on the backend side, Oracle and Sql server(versions from sql server 2000
and forward) do pretty much the same stuff -- just slightly different
flavors - like Oracle might use barbeque sauce on pivoting functions
where sql server may use sweet and sour - it is still all chicken (so to
speak).

As for front ends for industrial scale jobs, I have moved on the .Net
from Access. I experimented with Java, and even though Java is more
open source, if you are dealing with Microsoft DB's (sql server) or even
Oracle, I have not found anything easier to use than .Net.

.Net is lower level than Access - doesn't have all the overhead from the
subclassing (and of course, it doesn't have all of the builtin features
of Access - so you have to program those guys in yourself), but you have
a much more streamlined application that is only going to do what you
program it to do.

As for Rapid Application development - for smaller scale apps, Access
will remain the king - probably forever, and is useable by programmers
and non-programmers.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[dreadnought8]

Thanks GH, that's just the kind of intelligent feedback I was looking
for.