Access 和 Oracle 之间有什么区别？

What are the differences between Access and Oracle?

提问by hrishi

I'd like to know what are the differences between Access and Oracle.

我想知道 Access 和 Oracle 之间有什么区别。

回答by David-W-Fenton

Again, a thread in which all the answers miss the point.

再次，所有答案都没有抓住重点的线程。

Access is a development tool for creating database front ends.

Access 是用于创建数据库前端的开发工具。

Oracle is just a database, not a development tool (though it apparently now ships with a set of development tools, that doesn't change the fact that Oracle is still a database, not a development platform).

Oracle 只是一个数据库，而不是一个开发工具（尽管它现在显然附带了一组开发工具，但这并没有改变 Oracle 仍然是一个数据库，而不是一个开发平台的事实）。

Perhaps you think your question is about two database engines, but as written, it's like asking "what is the difference between an automobile and a restaurant?"

也许您认为您的问题是关于两个数据库引擎，但正如所写的，这就像问“汽车和餐厅之间有什么区别？”

Of course, Access ships with a default database engine, Jet/ACE, and it's likely that's what you mean to be asking about. I certainly can't blame some people with only casual contact with Microsoft products for failing to know this, as Microsoft has obfuscated the distinction between the two, both in its documentation and in its naming of the data providers that you have to use in connect strings.

当然，Access 附带一个默认的数据库引擎 Jet/ACE，这很可能就是您要询问的意思。我当然不能责怪一些与 Microsoft 产品只是偶然接触的人不知道这一点，因为 Microsoft 在其文档和您必须在连接中使用的数据提供程序的命名中混淆了两者之间的区别字符串。

But that's no reason to remain ignorant.

但这不是保持无知的理由。

Key differences between Jet/ACE and Oracle (or any database server):

Jet/ACE 和 Oracle（或任何数据库服务器）之间的主要区别：

A. with Jet/ACE there is no server-side process managing and martialing client data requests. All clients to a Jet/ACE database are operating directly on the file, using a locking file.This means certain things:

A. 使用 Jet/ACE，没有管理和控制客户端数据请求的服务器端进程。Jet/ACE 数据库的所有客户端都使用锁定文件直接对文件进行操作。这意味着某些事情：

• data in Jet/ACE files can never be as secure as the data stored in a server database.

• Jet/ACE will pull more data across the wire (it needs metadata and index pages in order to request the desired data pages from the file system), although with proper indexing and query design, it's not nearly as inefficient as the anti-Access bigots would like to believe.

• Jet/ACE has no transaction logging, and because its data file is being opened simultaneously by multiple users, is open to the risk of corruption.

• Jet/ACE 文件中的数据永远不会像存储在服务器数据库中的数据那样安全。

• Jet/ACE 将通过网络拉取更多数据（它需要元数据和索引页以便从文件系统请求所需的数据页），尽管通过适当的索引和查询设计，它的效率并不像反访问偏执狂那样低愿意相信。

• Jet/ACE 没有事务日志，并且因为它的数据文件被多个用户同时打开，所以存在损坏的风险。

B. Oracle is significantly more expensive to deploy and maintain than a simple file-based system like Jet/ACE.This means that:

B. 与简单的基于文件的系统（如 Jet/ACE）相比，Oracle 的部署和维护成本要高得多。这意味着：

• For small businesses with small user populations and no significant security issues, a Jet/ACE back end can be perfectly suitable, assuming a properly-designed front end and proper deployment (e.g., splitting front end/back end, not sharing front end, etc.).

• Some businesses are small enough that they don't have a dedicated server on which to run a behemoth like Oracle. No doubt Oracle has a desktop version, but it's inadvisable, in my opinion, to run a program like that on anything other than a dedicated server, since user operations could interfere with the reliability of the database back end.

• Because of the tight integration between Access as front-end development tool and Jet/ACE, it's significantly easier for non-technical users to put together useful database applications without needing to be an expert on Oracle (or SQL Server or whatever).

• 对于用户规模较小且没有重大安全问题的小型企业，Jet/ACE 后端可以非常合适，前提是前端设计合理，部署合理（例如，拆分前端/后端，不共享前端等） .)

• 一些企业规模小到没有专门的服务器来运行像 Oracle 这样的庞然大物。毫无疑问，Oracle 有桌面版本，但在我看来，在专用服务器以外的任何设备上运行这样的程序是不可取的，因为用户操作可能会干扰数据库后端的可靠性。

• 由于 Access 作为前端开发工具与 Jet/ACE 之间的紧密集成，非技术用户可以更轻松地将有用的数据库应用程序组合在一起，而无需成为 Oracle（或 SQL Server 或其他方面的专家）。

So, ultimately, choosing between a server back end and a file-based solution like Jet/ACE is going to depend mostly on how valuable in terms of money the increased security, scalability and reliability of a server database is. For any number of small businesses, there is really no contest -- Jet/ACE wins hands down.

因此，最终，在服务器后端和基于文件的解决方案（如 Jet/ACE）之间进行选择将主要取决于服务器数据库增加的安全性、可扩展性和可靠性在金钱方面的价值。对于任何数量的小企业来说，确实没有竞争——Jet/ACE 胜出。

But there are a lot of applications out there that have started life with a Jet/ACE back end that really ought to be upsized to a server back end. Oracle doesn't seem to me like a very good candidate for that unless the upsizing is being done within an organization that is already an Oracle shop. SQL Server Express seems to me like the much more natural upgrade path for such an app (though it has a 4GB limit on the total size of the database, so you're not gaining all that much over Jet/ACE's 2GB limit), and one could certain consider open-source databases like MySQL and PostgreSQL that are entirely cost-free in terms of licensing.

但是有很多应用程序已经开始使用 Jet/ACE 后端，它们确实应该升级为服务器后端。在我看来，甲骨文并不是一个很好的候选人，除非在已经是甲骨文商店的组织内进行扩大规模。在我看来，SQL Server Express 更喜欢这种应用程序的更自然的升级路径（尽管它对数据库的总大小有 4GB 的限制，因此您并没有获得超过 Jet/ACE 的 2GB 限制的那么多），并且人们肯定可以考虑像 MySQL 和 PostgreSQL 这样的开源数据库，它们在许可方面是完全免费的。

The little old lady doesn't need a Mack truck to drive to church every Sunday morning. Likewise, the farmer shipping potatoes cross country would be wise to use something other than a Toyota Yaris. But the Yaris may be perfect for the little old lady, and the Mack truck exactly right for the farmer shipping his potatoes.

小老太太不需要麦克卡车每周日早上开车去教堂。同样，跨国运输土豆的农民最好使用丰田雅力士以外的其他东西。但是 Yaris 可能非常适合小老太太，而 Mack 卡车完全适合运送土豆的农民。

It's all a matter of picking the right tools for the job at hand.

关键在于为手头的工作选择合适的工具。

回答by rahul

What are the differences between Microsoft Access and Oracle?

Microsoft Access 和 Oracle 之间有什么区别？

Difference One: Access is a small database system, which will allow you to create a small-medium sized database with minimum security features. Oracle can be integrated with NT security, which gives tight control over who can and cant get to your data.

Difference Two: Access is a lot slower over networks as all the processing is performed on the client machine, whereas with a large DBMS like Oracle (Or SQL Server), you send only a query (such as "SELECT * FROM Customers WHERE CustID=23;"), and only the required results are returned. If you ran this across a network with Access, all the records would be sent across the network, leaving the client machine to select the required record.

Difference Three: Access gets really slow if you try to connect more than 10 users (depending on the size of the DB and how it is being utilized), where are Oracle can accept hundreds/thousands of simultaneous connections (Depending upon the server it is sitting on).

The final major difference is that of triggers and stored procedures. Access can NOT store procedures which can be executed remotely. You can write functions and with a bit of "jiggery-pokery" get the server side to execute them vy setting flags or something in your database and getting the server to check at pre-defined intervals if the flag is set. With Oracle/(Other Large DBMS's) you can call the stored procedures and they will be executed on command on the server side. Triggers are like events on an Access form, such as "After-Update". When you update a record into Oracle, you can set triggers to automatically run procedures to check for pre-defined rules or whatever.

区别一：Access 是一个小型数据库系统，它允许您创建一个具有最低安全功能的中小型数据库。Oracle 可以与 NT 安全集成，从而严格控制谁可以访问您的数据，谁不能访问您的数据。

区别二：通过网络访问要慢得多，因为所有处理都在客户端机器上执行，而对于像 Oracle（或 SQL Server）这样的大型 DBMS，您只发送一个查询（例如“SELECT * FROM Customers WHERE CustID= 23;")，并且只返回所需的结果。如果您使用 Access 在网络上运行此程序，则所有记录都将通过网络发送，让客户端计算机选择所需的记录。

区别三：如果您尝试连接 10 个以上的用户（取决于数据库的大小及其使用方式），访问会变得非常慢，Oracle 在哪里可以接受成百上千的同时连接（取决于它是哪个服务器）坐在上面）。

最后一个主要区别是触发器和存储过程的区别。Access 不能存储可以远程执行的过程。您可以编写函数，并通过一些“jiggery-pokery”让服务器端通过设置标志或数据库中的某些内容来执行它们，并让服务器以预定义的时间间隔检查是否设置了标志。使用 Oracle/（其他大型 DBMS），您可以调用存储过程，它们将在服务器端按命令执行。触发器类似于 Access 表单上的事件，例如“更新后”。当您将记录更新到 Oracle 中时，您可以设置触发器以自动运行程序以检查预定义的规则或其他任何内容。

from here

从这里

回答by JohnFx

I can't beleive no one has said it yet....

我不敢相信还没有人说过......

About $400,000.

大约 40 万美元。

回答by paxdiablo

One is a heap of rubbish, and the other one is from Microsoft.

一个是一堆垃圾，另一个来自微软。

No, seriously, someone set me straight if I'm wrong. Can Oracle tell the difference between an empty string and a NULL yet? I'd really like to know.

不，说真的，如果我错了，有人会纠正我。Oracle 能分辨空字符串和 NULL 之间的区别吗？我真的很想知道。

Ah, my beloved DB2 ;-)

啊，我心爱的 DB2 ;-)

回答by Abhay

Difference in what terms?

区别在哪些方面？

Access is a database provided by Microsoft. Oracle is a database provided by Oracle corporation.

Access 是 Microsoft 提供的数据库。Oracle 是 Oracle 公司提供的数据库。

Refer to this linkfor a detailed discussion for differences in how they implement SQL.

有关它们如何实现 SQL 的差异的详细讨论，请参阅此链接。

回答by calweb

Adding on to the previous answers, another major difference with Oracle and MS Access is concurrency. Oracle as well as MySQL and postgreSQL and some others can handle multiple 'transactions' and 'commits' Basically, if you use a database with a website, a more secure and stable database would be Oracle, likewise an application.

除了前面的答案之外，Oracle 和 MS Access 的另一个主要区别是并发性。Oracle 以及 MySQL 和 postgreSQL 以及其他一些可以处理多个“事务”和“提交”。基本上，如果您使用带有网站的数据库，那么更安全和稳定的数据库将是 Oracle，同样是一个应用程序。

If you use MS Access, you're likely to run into problems with a lot of web traffic, especially if the content in the database is being written and updated by multiple people.

如果您使用 MS Access，您可能会遇到大量 Web 流量的问题，尤其是当数据库中的内容由多人编写和更新时。

Oracle and the like can be scaled to very large sizes....think Myspace or a Country's financial system (ERP).

Oracle 等可以扩展到非常大的规模......想想 Myspace 或一个国家的金融系统 (ERP)。

回答by JeffO

A.) Oracle to Access is like Unix to Windows 95. B.) Thousands of Dollars C.) Access has a database engine, and application front-end, hooks into other Office Apps and that paperclip thingy. Oracle has a command prompt and a blinking cursor with a big ass database behind it that can process more data than God and/or Larry Ellison.

A.) Oracle 到 Access 就像 Unix 到 Windows 95。B.) 数千美元 C.) Access 有一个数据库引擎和应用程序前端，挂钩到其他 Office 应用程序和那个回形针的东西。Oracle 有一个命令提示符和一个闪烁的光标，背后有一个大数据库，可以处理比上帝和/或拉里·埃里森更多的数据。

回答by onedaywhen

Turn the question around: what do Access and Oracle have in common?

扭转问题：Access 和 Oracle 有什么共同点？

1. They both store data logically as rows, columns and tables (so-called 'relational' but not truly relational).
2. Erm...
3. That's it!

1. 它们都将数据逻辑地存储为行、列和表（所谓的“关系”但不是真正的关系）。
2. 嗯...
3. 就是这样！

The Access database engine is a nifty file sharing system with a SQL-style data gateway; not truly SQL because, although some of its ANSI-92 Query Mode resembles the SQL-92 Standard, other areas are nowhere near compliant.**

Access 数据库引擎是一个漂亮的文件共享系统，带有 SQL 风格的数据网关；不是真正的 SQL，因为虽然它的一些 ANSI-92 查询模式类似于 SQL-92 标准，但其他领域远不符合要求。**

Oracle is an industrial-strength server database management system with a high degree of compliance with the SQL Standards plus extensions (tiggers, procedures, etc).

Oracle 是一个工业强度的服务器数据库管理系统，高度符合 SQL 标准和扩展（触发器、过程等）。

---

---

** The SQL Server team wanted to make Jet SQL-92 compliant for the Jet 4.0 release in the late 1990s but were overruled by the Windows team because doing so would have broken internal Microsoft systems that had been coded on assumptions e.g. the quirky way that OUTER JOINs workwhere the ONand WHEREclauses are not distinguished between. It was later made Microsoft 'law' that code written against one version of Jet would be guaranteed to run against all future versions of Jet. I assume this is why from Access2007 the engine is no longer Jet but now ACE: the Access team couldn't develop Jet because of the 'law' so they started anew with their private branch of the engine code. So don't go calling ACE 'Jet', it is decidedly not Jet for very good reason! That said, most (all?) of the changes to the engine for ACE 2007 was for SharePoint integration (multivalued types, attachment type) and not qualms about ditching functionality they did not need (replication, user level security). So there's another difference: whereas Oracle arguably develop products for end users, Microsoft change (or prevent change) to the Access data engine for their own internal purposes then peddle it on (or force their decisions upon) MS Office Access users ;)

** SQL Server 团队希望在 1990 年代后期使 Jet SQL-92 兼容 Jet 4.0 版本，但被 Windows 团队否决了，因为这样做会破坏基于假设编码的 Microsoft 内部系统，例如奇怪的方式OUTER JOINs 工作的地方ON和WHERE子句之间没有区别。后来微软制定了“法律”，即保证针对一个版本的 Jet 编写的代码可以针对所有未来版本的 Jet 运行。我想这就是为什么从 Access2007 引擎不再是 Jet 而现在是 ACE：由于“法律”，Access 团队无法开发 Jet，所以他们重新开始使用引擎代码的私有分支。所以不要叫 ACE 'Jet'，它绝对不是 Jet 有充分的理由！也就是说，ACE 2007 引擎的大部分（全部？）更改都是针对 SharePoint 集成（多值类型、附件类型），而不是担心放弃他们不需要的功能（复制、用户级安全性）。所以还有另一个区别：虽然甲骨文可以说是为最终用户开发产品，

回答by Jay

A quick summary:

快速总结：

Access is cheap, simplistic, lightweight, but inflexible and historically buggy. Oracle is expensive, heavy, has no front end but sophisticated and well tested.

Access 便宜、简单、轻量，但不灵活且历史悠久。Oracle 既昂贵又笨重，没有前端，但复杂且经过良好测试。