MS Access前端:有哪些风险? [英] MS Access front-end: What are the risks?

问题描述

我正在考虑为Oracle数据库构建MS Access前端.

I'm considering building a MS Access front-end for an Oracle database.

我不是开发人员(我是公共工程人员)，但是我确实知道围绕MS Access和Oracle的方式.用户数量将是5，可能会增加到10-20.前端大部分是报告，数据输入的格式为奇数.安全不是主要问题；安全性不是主要问题.由数据库处理，并且信息不敏感.

I'm not a developer (I'm a public works guy), but I do know my way around MS Access and Oracle. The number of users would be 5, possibly growing to 10-20. The front end would be mostly reports, with the odd form for data entry. Security isn't a primary concern; that's handled by the database, and the information isn't sensitive.

我知道MS Access项目通常最终会成为灾难性的怪物.据我所知，MS Access并不是要成为企业系统.

I'm aware that MS Access projects often end up being disastrous monstrosities. As far as I know, MS Access is not meant to be an enterprise system.

但是我正在考虑，因为，我没有其他选择.我不在I.T.和我的I.T.部门根本没有资源可以提供帮助.在我的组织中，一个合适的企业级现成系统离现在还有5到10年的路程.我等不了那么久.相反，我可以使用MS Access.

Yet I'm considering it, because, well, I don't have any other options. I'm not in I.T., and my I.T. department simply doesn't have the resources to help. And in my organization, a proper, enterprise, out-of-the-box system is 5-10 years away. I can't wait that long. Instead, I have MS Access to work with.

我希望，如果我坚持一些关键原则，那么前端将不会最终变成脆弱，灾难性的怪兽，而是成为一个可持续且强大的系统.

I'm hoping that if I stick to a few key principles, that the front-end won't end up as a fragile, disastrous monstrosity, but rather be a sustainable and robust system.

我希望:

• 将其尽可能简单地保留下来.如果功能不是绝对必要的，则不要实现它.迫使利益相关者证明其要求.
• 仅将其视为原型，而不应将其视为正式的企业系统.让所有利益相关者郑重宣誓最终将其迁移到适当的企业系统中.
• 配置，请勿自定义.仅自定义(VBA)作为绝对不得已的方法.即使这样，在进行定制之前，请考虑不做任何事情.我之所以这样说，是因为我是办公室中唯一会写脚本的人，而且我也不是那么擅长.
• 定期进行消防演习".如果事情破裂了，而我又没有帮助，那会发生什么?定期举行培训/知识共享课程，以向同事介绍该系统.
• 像照看花园一样趋向于系统.保持领先.通过使其变得更简单，更有效并消除不必要的功能来不断改进它.

说了这么多，即使我设法做到了这些，我猜测在MS Access中建立企业系统仍然存在一些问题.

With all this said, even if I manage to do these things, I'm guessing that there are still problems associated making an enterprise system in MS Access.

与企业MS Access前端相关的风险和内在问题是什么?

What are the risks and inherent problems associated with an enterprise MS Access front-end?

推荐答案

首先，您应该给自己荣誉！您绝对不会缺乏经验.恰恰相反.您构建和维护系统的方法听起来是一流的.关于您的限制，听起来好像您需要报告工具，并且听起来Access是您唯一的选择.我知道这里有一条建议建议使用Oracle Apex的注释，但是我认为这不是您的选择.使用Oracle的本机访问方法的产品肯定会比Access表现更好，但这并不意味着您已经走到了尽头.如果您了解访问限制，则访问是一个功能强大的工具(因此，我不仅仅意味着2GB的文件大小限制；我怀疑您会遇到这种限制).以下是我可以提供的一些建议，希望这听起来并不陌生:

Firstly, you should give yourself credit! You definitely don't sound inexperienced; quite the contrary. Your approach to building and maintaining a system sounds top notch. Regarding your limitation, it sounds like you need a reporting tool, and it sounds like Access is your only option. I know there is a comment here that suggests Oracle Apex, but I assume that is not an option for you. A product using native access methods to Oracle would most certainly perform better rather than Access, but that doesn't mean you have hit a dead end. Access is a powerful tool if you understand its limitations (and by that, I don't just mean the 2GB file size limit; I doubt you would run into that). Here are a few of suggestions that I can offer, and hopefully this doesn't sound foreign:

1. 如果您有能力将SQL作为Oracle中的存储过程或视图编写，则可以这样做.
2. 不要链接到Oracle中的表，这会非常缓慢.
3. 不要使用ADO将数据从Oracle传输到Access，这也很慢，因为它需要逐行处理.
4. 使用SQL直通查询连接到Oracle并执行您的存储过程/视图/SQL.这将是您最快的选择，因为Access仅将您的查询发送到服务器以执行.它本身并不会做任何事情来运行它(或估计如何运行它).
5. 尝试在Oracle内部执行所有逻辑.意思是，如果您没有编写存储过程的能力，并且说需要创建临时表，则可以在oracle会话中或在通过SQL传递查询执行的SQL脚本中执行此操作.
6. 如果需要将数据传输到Access，请尝试限制它.您可能不会创建200页的报告，所以请不要返回不需要访问的数据.利用服务器的处理能力来发挥自己的优势.
7. 假设您已完成所有操作，现在可以分发Access文件了.不要将数据库放在网络驱动器上并与用户共享.给每个用户自己的副本.现在，由于诸如版本控制之类的事情开始起作用，因此这个主题本身将是一个漫长的讨论，但是在这里我将不再赘述.如果用户始终可以每次访问网站并每次都下载干净的Access文件，则可以这样做.如果不是这样，它们将始终启动其本地副本，则需要在Access中实现对任何本地数据的一些清理例程，然后启用关闭时压缩"设置，以消除可能影响性能的混乱情况.
8. 如果您对Oracle的查询效果不佳，并且您的报告最终运行缓慢，请不要害怕寻求帮助. DBA不会喜欢您做任何会影响其数据库性能的事情，因此请利用它们来帮助您调整SQL.

1. If you have the ability to write your SQL as stored procedures or views inside Oracle, then do that.
2. Don't link to tables in Oracle, that will be painfully slow.
3. Don't use ADO to transfer data from Oracle to Access, that too will be slow, as it will require row-by-row handling.
4. Use SQL pass-through queries to connect to Oracle and execute your stored procedures/views/SQL. This will be your fastest option, as Access only sends your query to the server to execute; it does not do anything itself to run it (or estimate how to run it).
5. Try to perform all of your logic inside Oracle. Meaning, if you don't have the capability to write stored procedures, and say you need to create temporary tables, then do that in the oracle session, or inside the SQL script that you execute through the SQL pass-through query.
6. If you need to transfer data to Access, try to limit it. You are probably aren't creating a 200-page report, so don't return data that you don't need to Access; utilize the server's processing power to your advantage.
7. Let's assume you got all this done, and are now ready to distribute the Access file. Don't put the database on a network drive and share it with users. Give each user their own copy. Now, this topic in itself would be a long discussion, as things like version control come into play, but I won't get into that here. If the users can always go to a website and download a clean Access file each time they need to use it, then do that. If not, and they will always launch their local copy, you need to implement some cleanup routines of any local data in Access, followed by enabling the compact-on-close setting, so it removes the clutter that could affect performance.
8. If your queries against Oracle don't perform well, and your reports end up being slow, don't be afraid to reach out for help. DBA's won't like anything you do that will affect their database performance, so utilize them for assistance with tuning your SQL.

这篇关于MS Access前端:有哪些风险?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！