Access front-end: Oracle vs SQL Server backend

[John]

I need to upsize my Access 2002 "database" to a larger database. I
would like to use Access projects to manage the database. With this
preference, it would seem MS SQL Server 2000 is the ideal way to go.

Nothwithstanding scalability, performance, pricing, my question is
this: what are the distinct advantages MS SQL Server [may] have over
Oracle when used in conjunction with Access? What are the drawbacks, if
any, to using an Access project with ODBC links to an Oracle database?

[Larry Linson]

In my not-so-humble opinion, Microsoft SQL Server is the simplest,
easiest-to-administer "heavy-duty" server database I have encountered. It
has user-friendly and excellent administrative tools. But, every server
database I've encountered requires administration and frequent "tender
loving care".

Access can be used to easily create user-friendly front-ends for this (and
for any other ODBC-compliant server DB). And, as server databases go,
Microsoft SQL Server is not overly expensive -- it comes in several
different versions, from a free version for a few users, a Small Business
Edition for more, and the full version can be expanded to truly impressive
data and user capacity.

Oracle has a good reputation in the heavy-duty, industrial-strength database
arena, but it is not inexpensive, and most of the people I know who have
used it have said that you will likely need to hire Oracle's engineers to
install and tune it (at truly staggering rates).

These are not, of course, the only options. Comparing them all would be
beyond the scope of a newsgroup response, as would a detailed comparison of
MS SQL Server and Oracle -- if you could find someone with the in-depth
experience with both who had the time to create such a response.

Larry Linson
Microsoft Access MVP

"John" <so*********@hotmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I need to upsize my Access 2002 "database" to a larger database. I
would like to use Access projects to manage the database. With this
preference, it would seem MS SQL Server 2000 is the ideal way to go.

Nothwithstanding scalability, performance, pricing, my question is
this: what are the distinct advantages MS SQL Server [may] have over
Oracle when used in conjunction with Access? What are the drawbacks, if
any, to using an Access project with ODBC links to an Oracle database?

[John]

Can I use Access projects with Oracle and get the same features and
benefits of using Access projects with a MS SQL Server database? Or
would be less efficient becuase of linking issues?

[Larry Linson]

"John" wrote

Can I use Access projects with Oracle
and get the same features and benefits
of using Access projects with a MS SQL
Server database? Or would be less
efficient becuase of linking issues?

I have only used Access projects (ADP/ADE) with Microsoft SQL Server, and,
as far as I know, that is the only server with which they CAN be used.
"Classic ADO", of course, is not limited to ADP/ADE, and can be used with
any server database that has an ADO data provider.

As to "benefits of using ADP/ADE", knowledgeable Microsoft insiders now
recommend MDB-DAO-Jet-ODBC-server, as a rule, over ADP-ADODB-MSSQLServer. At
least one poster here, a fan of ADP, was complaining that Microsoft Support
had told him that ADP was being "deprecated in the next version of Access".
I have not heard/seen a public announcement to that effect, however.

My use of ADP was quite brief compared to my years of experience with MDB,
but I saw little advantage and a number of drawbacks... some of which people
have documented here. A majority of the paying work I have done with Access
was Access clients to various server databases, including Informix, Sybase
SQL Anywhere, Sybase SQL Server, and MS SQL Server. A number of people who
have used Access clients to Oracle have told me that
Access-DAO-Jet-ODBC-Oracle worked very nicely for them.

Larry Linson
Microsoft Access MVP

[Terrell Miller]

Larry Linson wrote:

Oracle has a good reputation in the heavy-duty, industrial-strength database
arena, but it is not inexpensive, and most of the people I know who have
used it have said that you will likely need to hire Oracle's engineers to
install and tune it (at truly staggering rates).

These are not, of course, the only options. Comparing them all would be
beyond the scope of a newsgroup response, as would a detailed comparison of
MS SQL Server and Oracle -- if you could find someone with the in-depth
experience with both who had the time to create such a response.

I'd say that about sums it up: use Oracle for gigauser enterprise apps,
SQL Server for stuff that a hundred or so concurrent users will touch at
once. YMMV, but that's a rough baseline.

I've used Access as an ODBC frontend for Oracle db's in a dotcom and a
large telecom, and I'm using it as a frontend for SQL2k in a government
agency now.

FWIW, we get better performance linking to SQL than I ever did with
Oracle. ODBC drivers into an Oracle table can be s l o w and have a
nasty habit of getting hung up (thereby locking the table) if there's a
network glitch. Either our current network guys are *real* sharp adn
keep the system humming, or SQL is just more robust and forgiving of
connection hiccups.

If you're used to Access then maintaining your SQL db will be much
easier than if you have your data in Oracle. SQL's Enterprise Manager
console is really just Access on steroids from a usability standpoint.
It will look and feel very familiar to you. Not so with Oracle's SQL
Plus interface, which is the only one I ever used. That was like going
back to DOS command-line prompts all over again.

So I'd say your best bet is to try SQL Server first.

HTH,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

[Trevor Best]

Terrell Miller wrote:

Larry Linson wrote:

Oracle has a good reputation in the heavy-duty, industrial-strength
database
arena, but it is not inexpensive, and most of the people I know who have
used it have said that you will likely need to hire Oracle's engineers to
install and tune it (at truly staggering rates).

These are not, of course, the only options. Comparing them all would be
beyond the scope of a newsgroup response, as would a detailed
comparison of
MS SQL Server and Oracle -- if you could find someone with the in-depth
experience with both who had the time to create such a response.

I'd say that about sums it up: use Oracle for gigauser enterprise apps,
SQL Server for stuff that a hundred or so concurrent users will touch at
once. YMMV, but that's a rough baseline.

A few (actually about 1.3 million) gamers recently fell foul of Oracle's
million user limit :-)

--
This sig left intentionally blank