Excel/Access 的替换或迁移策略
有没有一种方法可以提供最终用户喜欢的 Excel/Access 开发的灵活性，同时灌输集中式 IT 管理，以便数据和逻辑安全、备份、版本控制等。常见的选项是用 C#/ASP.Net 重写/Java/Python/Your Choice，但这剥夺了用户的控制权。有更好的方法吗？您在自己的网站上做了什么？
用户创建非常有用的 Excel/Access 迷你应用程序是 IT 部门希望控制的一个普遍问题。用户喜欢 Excel 提供的灵活性，尤其是即时更改、绘图和数据导入/导出。在 Access 中，我们拥有出色的 QBE。缺点是，不久之后就会出现大量失控的电子表格/MDB，这些电子表格/MDB 是关键任务，其中有很多业务逻辑难以理解，代码也很脆弱，支持它们会很痛苦，尤其是在员工离职时。
这让 IT 部门陷入了尴尬的境地，他们想要支持这些应用程序，但对它们了解不够。这变得更加困难，因为它们通常在零文档的情况下不安全。
Is there a way of offering the flexibility of Excel/Access development that end users love while instilling centralised IT management so data and logic is secure, backed up, version controlled etc. The common options are to re-write in C#/ASP.Net/Java/Python/Your Choice, but that takes away control from the users. Is there a better way, and what do you do at your site?
There is a universal issue of users creating fantastically useful Excel/Access mini-apps that the IT department would like to bring under control. Users love the flexibility that Excel affords, especially on the fly changes, graphing and data import/export. In Access we have brilliant QBE. The downside is that after a short while there are legions of out of control spreadsheets/mdbs which are mission critical, with lots poorly understood business logic, and brittle code, they're a pain to support especially as staff move on.
This puts the IT dept in an awkward spot, they'd like to support these apps, but don't know enough about them. This is made more difficult as they are typically insecure with zero documentation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
我赞同凯文·罗斯的要点之一：
我认为任何拥有大量使用 Access/Excel 用户的 IT 部门都应该拥有至少一名经过适当培训且经验丰富的专家，负责在这些平台上开发应用程序。该人将成为中间人，以确保：
IT 的优先级和策略在本地应用程序中得到正确实施。
最终用户获得专家的帮助，将他们的本土成果转化为更稳定和设计精良的东西。
最终
我赞同托尼的观点，即无论谁与最终用户一起修改这些应用程序以满足 IT 标准，都应该与用户并肩工作。 Access/Excel 专家应该是最终用户的倡导者，同时也是必须遵循的 IT 策略的倡导者。
我还认为 IT 部门可以配备一两名专家，但还应该配备一名全职专业 Access 和/或 Excel 开发人员作为顾问，因为在职人员可能可以处理日常问题和管理应用程序，同时可以请专业顾问进行规划和架构以及实施更复杂的功能集。
但所有这些都取决于组织的规模和所涉及的应用程序的数量。我不知道是否希望有一个除了 Access/Excel 专家之外什么也没有的人来领薪水，正是因为与顾问相比，所有受薪员工都会遇到这样的问题——员工们并没有看到像顾问那样广泛的问题。作为具有相同专业的活跃顾问，可能会遇到各种情况，因此顾问将拥有更广泛的经验。
当然，我认识到许多公司不喜欢外包任何东西，或者不喜欢外包那么重要的东西。我认为这是不明智的，但话又说回来，我是被决定这样做的人雇用的人！
I would second one of Kevin Ross's main points:
I think any IT department that has a lot of users using Access/Excel should have at least one properly trained and experienced specialist in developing apps on those platforms. That person would be the go-between to make sure that:
IT's priorities and policies get properly implemented in the home-grown apps.
the end users get expert help in converting their home-grown efforts into something more stable and well-designed.
I would second Tony's point that whoever works with the end users in revising these apps to meet IT standards should work side-by-side with the users. The Access/Excel specialist should be an advocate for the end users, but also for the IT policies that have to be followed.
I also think that an IT department could have a specialist or two on staff, but should also have a full-time professional Access and/or Excel developer as a consultant, since the on-staff people could probably handle day-to-day issues and management of the apps, while the professional consultant could be called in for planning and architecture and for the implementation of more complex feature sets.
But all of that would depend on the size of the organization and the number of apps involved. I don't know that it would be desirable to have someone on salary who is nothing but an Access/Excel specialist, precisely because of the problem you get with all salaried employees compared to consultants -- the employees don't see as wide a variety of situations as an active consultant with the same specialization is likely to see and thus the consultant is going to have broader experience.
Of course, I recognize that many companies do not like to outsource anything, or not something that important. I think that's unwise, but then again, I'm the person that gets hired by the people who decide to do it!
如果它是关键任务，并且是在 Access 或 Excel 中构建的，而且构建得很差，并且没有人理解它，那么可能是时候正确地重建它了。
If it's mission critical, and it's in Access or Excel, is built poorly, and no one understands it, it is probably time to rebuild it properly.
当“用户”控制时，通常意味着某个特定的人控制着架构、设计、编码和文档……只不过他们通常会省略文档步骤。作为软件开发的试金石的源代码控制和错误报告通常是缺失的。由于 Office 应用程序（代码模块通常嵌入到文档中）和 VBA（很少有 OOP，大多数 VBA 编码人员不使用 Implements 等）的性质，代码重用的实例很少。所有这些都意味着生成的应用程序不会受到适当的审查，并且质量可能会受到影响，这意味着可能会出现维护问题，特别是当某个用户离开时。我知道，因为我曾经是那个人；）
因此，为了满足 IT 部门的要求，需要应用正确的流程。一个“高级”用户可以继续拥有设计和编码，但会得到同行评审，也许需要技术作者和专门测试人员的服务，需要使用源代码控制，也许考虑与企业系统集成等。
When the 'users' are in control it usual means one particular person is in control of the architecture, design, coding and documentation... except they normally omit the documentation step. Source control and bug reporting, the touchstone of software development, is usually absent. Few instances of code reuse, due to the nature of Office apps (code modules usually embedded into documents) and VBA (little OOP, most VBA coders don't use Implements, etc). All this means that the resulting applications are not subject to get proper scrutiny and quality can suffer, meaning there are likely to be maintenace issues, escpecially when that one user leaves. I know because I used to be that person ;)
So in order to satisfy the IT department, the proper process needs to be applied. That one 'power' user can continue to own the design and coding but will get peer review, perhaps the serivces of a technical author and a dedicated tester, be required to use source control, perhaps consider integrating with enterprise systems, etc.
Excel/Access 的使用是不可避免的。它是可用的，而且仍然非常强大和灵活。最好的办法是提供一些关于文件的外观和设置的指南。如果每个人都使用类似的标准，那么文件的寿命将更长，效率更高，超出创建者在公司的任期。
There is no getting around the use of Excel/Access. It's what's available, and still very powerful and flexible. The best thing to do is offer some guidelines as to how files should look and be set up. If everyone is using similar standards then the files will live longer and more productive lives, beyond the creator's tenure at the company.
关于与人打交道和业务方面的事情，您已经得到了一些很好的答案。所以我的回答会更加技术性。
如果您要重新设计应用程序，请让开发人员与用户在同一办公室工作。鉴于用户每天或每两天都会更新。如果用户有任何小的建议，请在一两天内向用户提供。 超频繁的应用程序部署
为高级用户提供链接到的 Access MDB/ACCDB带有一堆起始查询的表。让他们创建所需的查询，将数据导出到 Excel 以供自己使用并分发给客户。
You've got some excellent answers regarding dealing with the folks and the business side of things. So my response will be more technical.
If you are going to redesign the app have the developers work in the same offices as the users. Given the users updates every day or two. If the users have any minor suggestions give those to the users within a day or two. Ultra Frequent Application Deployment
Give the power users an Access MDB/ACCDB linked to the tables with a bunch of starter queries. Let them create the queries they need to export the data to Excel for their own purposes and distribution to clients.
我曾经站在两边，我会追寻问题的根本原因。为什么用户要制作自己的小应用程序？因为当他们通过“正确”的渠道时，这太难/太贵/太耗时/永远不会有结果。
另一件事是他们往往非常了解业务，因此虽然他们的编码可能不是很好，但他们对需要做什么的了解非常好。
那么我们能做些什么来解决这个问题呢？我个人认为他们应该是 IT 内部的一个小团队，他们的工作（或他们的工作之一）是开发这些小型应用程序。他们应该与最终用户密切合作，而不是被锁在 IT 的象牙塔里。
在我目前的角色中，我处于非 IT 方面，我有一些非常重要的应用程序需要开发，因此我要求安装 Visual Studio 并在 SQL 服务器上提供一些空间。我的请求被拒绝了。所以我只是请求 SQL 服务器空间，再次请求被拒绝（每个请求大约需要一周的时间才能完成）所以最终我“卡住”在访问中。
现在，这些都是非常好的访问应用程序，具有版本控制、评论（震惊！）以及所有其他好东西，但最终我试图以“正确”的方式做事，最终被迫关闭访问路线。因此，当我的应用程序尝试扩大规模并且我引用了很长一段时间进行重写时，谁应该受到指责？
Having been of both sides of the fence I would go after the root cause of the problem. Why do uses make their own little apps? Because it is too hard/expensive/time consuming/never turns out right when they go through the “proper” channels.
The other thing is they tend to know the business very well so whilst their coding might not be very good their knowledge of what needs doing is very good.
So what can we do to combat this problem? I personally think their should be a small team of people within IT whose job (or one of their jobs) is to develop these small applications. They should work very closely with the end users and not be locked in the ivory tower of IT.
In my current role I’m on the non-IT side of the fence, I have a few quite major applications that needed to be developed so I asked for an install of visual studio and some space on an SQL server. I had my request denied. So I just asked for SQL server space, again request denied (each request taking about a week to go through) So in the end I’m “stuck” in access.
Now these are very nice access apps with version control, comments in the (shock!) and all the other nice things but at the end of the day I was trying to do things the “right” way and ended up being forced down the access route. So when my apps try to get scaled up and I’m quoting a long time for a rewrite who is to blame?
您是否考虑过将 SharePoint 用于部门级应用程序？许多专业开发人员会对使用 Sharepoint 进行“应用程序开发”的想法犹豫不决，但事实上，对于“高级用户”来说，它可能是开始将其数据和工具放入托管框架中的好方法。
使用 SharePoint，您可以管理网站的整体结构，然后在各自的部门内设置具有提升权限的用户。有一些很棒的第 3 方工具可以帮助您密切关注 SharePoint 网站中发生的情况。
SharePoint 无论如何都不是灵丹妙药，但它对于许多需要跟上数据列表的多用户应用程序来说非常有用。
（以下内容与我上面的答案并不真正相关，但您的问题确实击中要害，我想我会分享我类似的经验和见解。）
我们公司将在不久的将来。我站在“最终用户”方面，并且可以同情很多凯文·罗斯说道。有时 Access 和 Excel 就是我完成工作的最佳工具。
这里有一个例子：几年前，我被要求设计一个系统，用于为中国的供应商创建交货期为 3 个月的产品的采购订单。我们的 ERP 软件有一些用于采购的功能，但根本无法与我们面临的情况的复杂性相提并论。几年后，在经历了 Excel（
VLOOKUP是救星）、Access（“这就是人们使用关系数据库的原因。太棒了！）中的应用程序的多次迭代之后，回到 Excel（“让我们不要让事情变得如此复杂”），我仍然发现这些 Micorosft Office 应用程序是完成工作的最佳工具不使用这些工具来完成工作的成本是多少 。工作完成了吗？
很难找到一个完美的应用程序来允许用户端发挥最大的创造力，同时又允许 IT 部门“管理”它。一旦你认为你已经找到了一件事的解决方案，你就会意识到它并不能解决其他问题。我可以像以前在 Access 中那样在此解决方案中编写可打印报告吗？我可以编写复杂的 Excel 公式，将不同工作表中的多个数据源联系在一起（“你想让我学习什么？不，我从未听说过“SQuirre） L 查询”之前。
VLOOKUP就可以了，谢谢你了）？我可以通过电子邮件将结果发送给我部门的人员吗？它可以自动从我们的后端提取数据吗？我可以像在 Excel 和 Access 中那样编写自己的代码吗？我可以编写自己的代码（VBA 或其他代码）来使我的工作变得更轻松吗？最后，我可以给您遇到的任何 IT 经理的最佳建议是尊重公司的其他员工。让他们知道他们的工作很重要（即使这只对他们和隔壁办公桌的人有用）。让他们知道你并不是想让他们的工作变得很重要。不要认为他们是在办公生产力软件中创建关键任务应用程序的白痴他们只是想利用手头的工具完成工作，并且通常是非常有能力和聪明的人 。邀请他们与您一起探索不同的解决方案，而不是仅仅删除他们工具箱中现有的工具，然后用他们不知道如何使用的工具替换它们。
归根结底，如果您的用户足够聪明，可以通过在 Excel 和 Access 中创建复杂的应用程序来搬起石头砸自己的脚，那么他们可能足够聪明，能够学习使用适当的工具来完成相同的任务。投入时间和精力让他们参与这个过程，最终你将得到一个适合每个人的解决方案。
Have you considered looking at SharePoint for department-level applications? Many professional developers will balk at the idea of using Sharepoint for "application development," but it truthfully can be a great way for "power users" to start putting their data and tools in a managed framework.
With SharePoint, you can manage the overall structure of the site and then set up users with elevated permissions within their respective departments. There are some great 3rd-party tools to help with keeping an eye on what's going on in your SharePoint site.
SharePoint is not a silver bullet by any means, but it is great for many multi-user applicatinos that need to keep up with a list of data.
(The following is not really related to my above answer, but your question really hit home and I thought I'd share my similar experiences and insights.)
Our company will be going through a similar process in the near future. I'm on the "end user" side of things and can sympathize with a lot of what Kevin Ross said. Sometimes Access and Excel are simply the best tools available for me to get the job done.
Here's an example: I was asked several years ago to come up with a system for creating Purchase Orders to a vendor in China for product for which there is a 3 month lead time. Our ERP software had a few features for procurement, but nothing that even came close to the complexity of the situation we were facing. Years later, after going through several iterations of the application in Excel (
VLOOKUPwas a lifesaver), Access ("So that is why people using relational databases. Awesome!), and back in Excel ("let's not make this so complicated"), I still find that these Micorosft Office apps are the best tools to get the job done.What's the cost to not use these tools to get the job done?
It's so hard to find the perfect application to allow for maximum creativity on the user end but still allow IT to "manage" it. Once you think you've found a solution for one thing, you realize it doesn't do something else. Can I write I printable report in this solution like I used to do in Access? Can I write complicated Excel formulas that tie multiple data sources together from different sheets ("You want me to learn what? No, I've never heard of a "SQuirreL query" before.
VLOOKUPis just fine thankyouvermuch)? Can I e-mail the results to the people in my department? Can it automatically pull data from our back-end database like I do in Excel and Access? Can I write my own code, VBA or otherwise, to make my job easier? The list goes on.In the end, the best advice I can give to any IT manager in your situation is to respect the other workers at your company. Let them know their work is important (even if it's only useful to them and the guy at the next desk over). Let them know you are not trying to make their job harder. Don't assume they are morons for creating mission-critical applications in office productivity software; they are just trying to get the job done with the tools at hand and are usually quite capable and intelligent people. Invite them to explore different solutions with you instead of just removing the tools they currently have in their toolbox and then replacing them with ones they don't know how to use.
At the end of the day, if you have users who are smart enough to shoot themselves in the foot by creating complicated apps in Excel and Access, they are probably smart enough to learn to use the appropriate tools to accomplish the same tasks. Invest the time and energy to involve them in the process and you will have a solution that works for everyone at the end.
您可以尝试一种混合方法：允许您的用户使用 Excel/Access 自制自己的专用工具，但将关键任务的内容置于 IT 控制之下。有一些策略可以帮助您解决此问题：
You could try a hybrid approach: Allow your users to use Excel/Access to home-brew their own, specialized tools, but take the mission-critical stuff and put it under IT control. There are a few strategies that could help you with this: