旧版 MS Access 应用程序需要支持多个用户
我目前在一个 4 人团队中负责开发和维护旧版 MS Access 应用程序。
该应用程序相当大，有数百个表单、报告、查询和表格。
目前，我们将前端分为大约 7 个 mde 组件，每个组件本质上都是一个独立的应用程序，并由一个公共前端连接起来，而该前端本质上只是一个菜单 GUI。
我们使用链接表将此前端连接到 MS Access 后端，并在代码本身中使用 OpenDatabase(C:\access.mdb) 调用。 该应用程序已经存在了一段时间，因此使用 DAO 连接到 Access 97 后端。
这意味着应用程序的每个用户都有自己的数据库本地副本以进行更改。 我们有一个精心控制变更的环境，确保一次只有一个人可以处理数据，他们必须在将主数据库传递给下一个人之前验证所有更改。
温和地说，这种变更控制环境令人窒息，很快我们将需要在一段时间内进行更多数据更改，这使得单用户访问变得不可行。
因此，我们需要转向多用户访问，但我所说的多用户仅指大约 4 人。 这些人可能不在同一办公室，因此需要某种形式的远程数据库连接。
整个应用程序可能会在一两年内重新设计，将前端和后端都从 MS Access 移走。 但是，我们需要尽快进行多用户访问。
那么，实现多用户幸福的最快途径是什么？
我们正在考虑的建议包括：
- 设置 VPN，以便 MS Access 相信它正在访问常规网络驱动器。 这看起来会很慢，而且我不确定 VPN 是否足够可靠，但这只是我们追求的临时解决方案。
- 将 mdb 后端转换为供多用户远程使用的东西，例如 SQL Server。 我们只是不知道如何快速、轻松地完成此操作（例如，我们依赖于字段验证规则）。我们可能还必须转换回 MS Access 格式，因为其他应用程序接受相同的 .mdb 文件作为数据输入。
- 几乎任何事情都可以由一两个人在几个月内完成。
编辑：回应以下评论。
应用程序处理的数据是高度安全的关键数据。 它很少发生变化，并且在导出之前必须进行验证以表明不存在逻辑错误。 事实上，数据比应用程序本身受到更严格的限制！
这些数据以非平凡的方式相互关联。 因此，由于复杂的业务逻辑，对一个表中的记录进行更改可能会使另一表中的记录失效。 因此，目前，mdb 数据文件的一份副本被指定为主数据库。 任何时候只有一个人拥有主人。 如果您想对其进行更改，则必须从当前拥有该数据库的人员处获取该数据库。 这通常不是问题，因为数据变化很少，因此有足够的时间发生这种情况。
然而，一个巨大的变化即将到来，我们没有足够的时间以这种方式工作。 我们必须让多人同时处理数据。 现在我知道您可以在网络驱动器上共享 mdb 文件，并让同一办公室的多个人处理该文件，风险很小或没有风险，但我们需要来自不同公司的人员同时处理数据。 据我了解，设置 VPN 来共享数据是一个糟糕的计划。
我相信我们必须将后端从 MS Access 更改为 SQL Server 之类的东西。 但是以这种方式转换模式有多容易呢？ MS Access 表验证规则在 SQL Server 中如何表示？
I'm currently on a 4-person team tasked with the development and maintenance of a legacy MS Access application.
The application is quite large, with hundreds of forms, reports, queries, and tables.
Currently we have the front end split into about 7 mde components, each of which is essentially an application in its own right, joined by a common front end which is essentially just a menu GUI.
We use linked tables to connect this front end to an MS Access back end, using OpenDatabase(C:\access.mdb) calls in the code itself. This application has been around for a while, and thus uses DAO to connect to an Access 97 backend.
This means that every user of the application has their own local copy of the database for making changes. We have a carefully change-controlled environment that ensures that only one person can be working on the data at one time, they have to validate all their changes before passing the master database to the next person.
This change control environment is stifling to put it mildly, and soon we will have a need for more data changes to be made in a timeframe that makes single user access unviable.
So, we need to move to multi-user access, but by multi-user I mean only about 4 people. This people are probably not physically located at the same office, so some form of remote db connection is needed.
The whole application is likely to be re-worked in a year or two's time, moving both the front and back end away from MS Access. However, we need multi-user access ASAP.
So, what is the quickest path to multiuser bliss?
Suggestions we are considering include:
- Setting up a VPN so that MS Access believes it is accessing a regular network drive. This looks like it will be slow and I'm unsure if a VPN is reliable enough, but it is just a temporary solution we're after anyway.
- Converting the mdb backend into something intended for multiuser remote usage, like SQL Server. We just don't know how to do this quickly and easily (we rely on field validation rules for example) We would also presumably have to convert back to MS Access format as other applications accept the same .mdb files as data input.
- Just about anything that can be carried out by 1 or 2 people in a couple of months.
EDIT: Responding to the below comments.
The data that is processed by the application is highly safety critical data. It changes rarely, and must be validated to show there are no logical errors before being exported. In reality, the data is under heavier restrictions than the application itself!
The data is interrelated in non-trivial ways. As such, a change to a record in one table may invalidate a record in another table due to complicated business logic. As such, at the moment, one copy of the mdb data file is designated the master database. Only one person has the master at any one time. If you want to make changes to it, you have to obtain that database from the person who currently has it. This is usually not a problem as data changes rarely enough that there's enough time for this to occur.
However, a large change is coming up which we haven't been given enough time to work this way. We have to have multiple people working on the data at once. Now I'm aware that you can share the mdb file on a network drive and have multiple people in the same office work on that file with little or no risk, but we need people from different companies to work on the data at the same time. as I understand it, setting up a VPN to share the data across is a bad plan.
I believe we have to change the backend away from MS Access and move to something like SQL server. But how easy is it to convert a schema in this fashion? How are MS Access table validation rules represented in SQL Server?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
作为一般规则，该框的访问权限是作为文件共享访问多用户。 这意味着您可以获取后端数据库（mdb 文件）并将其放在服务器上的共享文件夹中。 这将允许您办公室中的几个人同时运行该应用程序。 然而，这意味着我们正在谈论典型的办公室局域网。 当您开始谈论远程连接、VPN 和广域网 (WANS) 时，使用访问作为文件共享并不稳定。
因此，如果典型的办公网络环境中只有三四个人，那么根据应用程序，很可能您可以简单地将后端放在服务器上的共享文件夹上，并继续将所有前端部署在每个服务器上计算机，并且它们链接到共享文件夹上的那个数据库后端 (mdb) 文件。 MS access 通过这种方式工作得很好。
然而，对于谈论某种 VPN 或 WAN，那么一种可能的解决方案是将后端 mdb 文件移动到 SQL Server，并继续使用当前应用程序中的所有表单、报告等（您的大多数应用程序将运行和以前一样）。
另一项值得考虑的伟大技术是瘦客户端，或所谓的终端服务。 终端服务只是远程桌面系统的一个精美版本。 TS 允许人们在相当有限的带宽上从远程位置运行和使用该应用程序。
但是，如果您谈论的是典型办公室 LAN 上的三到四个用户，那么您的应用程序很可能只需进行很少的修改即可运行，并且您只需将后端数据库文件移动到服务器上某处的共享文件夹即可。 不过，我不能强调，只有当所有人都在同一个小型办公室 LAN 上，而不是某种远程连接或 WAN/VPN 时，这才有效。 因此，在 WAN/VPN 的情况下，请使用终端服务，或者考虑将后端移至 SQL Server，并继续按原样使用应用程序前端。
编辑-更多信息：
好的，有了更多信息，我们就可以继续前进了。 如前所述，ms-access 是开箱即用的多用户。 您需要来自不同地点的人员来处理这些数据。 因此，这意味着无论存在不同的位置问题，您的应用程序都必须设置为具有多用户功能。 一旦您为多用户设置了应用程序，那么您就可以解决允许人们从不同位置使用该软件的问题。
这与我们管理公司圣诞晚会的东西没有什么不同。 如果我们的设计是在圣诞晚会后删除整个文件以重新开始明年的圣诞晚会，那么我们仍然可以允许该应用程序多次使用。 然而，由于您的设计，您不能同时举办多个圣诞派对。 因此，在这种情况下，应用程序并不是多用户的。 在这种类型的场景中，实际上可能会添加一个名为圣诞晚会年份表的新表。 然后，可以将应用程序中的所有表作为子表关联到该主表。 这样，您就可以针对此设计同时举办多个圣诞派对。 然后，当您启动应用程序时，系统会提示用户某种类型的列表，以选择您想要参加的圣诞派对。
因此，不要混淆上述两个单独的问题。 询问终端服务如何允许应用程序成为多用户是没有意义的，它没有做这样的事情。 TS 的作用是允许您采用已经是多用户的应用程序，并允许远程位置的人们使用该应用程序。 因此，TS 是一个允许人们在互联网上的任何远程位置运行和使用应用程序的系统。 然而，您的设计仍然会决定您的应用程序是否允许我们同时举办多个圣诞派对。
所以你不要让MS access成为多用户，MS access就是开箱即用的多用户，你不需要做任何事情，除了采用一些技术让不同地点的用户使用该应用程序。 这就是 TS 所做的事情，SQL Server 也可以为您做的事情。
如果您的设计只允许一个项目，那么我们可以允许来自世界不同地点的多个用户使用同一个应用程序，但由于应用程序的设计限制，他们只能拥有一个活动项目。
因此，所有表更新逻辑等都可以像以前一样工作。 您只需问一下，该应用程序现在是否允许一个用户退出该应用程序，而另一用户进入该应用程序并执行其工作？ 假设办公室里只有一台独立计算机。 不同的员工可以在一天中坐下来，为每个单独的项目使用一台计算机和一个具有一个后端的应用程序吗？
因此，使用 SQL Server 或终端服务并不会让您的应用程序比现在更多（或更少）多用户。 这些技术肯定可以增加可以同时使用该应用程序的用户数量。
所以MS access现在是多用户的。 然而，SQL Server 或 TS 所做的是在用户如何远程连接到该应用程序方面提供更大的灵活性。
As a general rule acccess right of the box is accesses multi user as a file share. What this means is that you could take the backend database (mdb file) and put it on a shared folder on a server. This would allow a few people in your office to run the application at the same time. However that means we’re talking about a typical office LAN. When you start talking about remote connections and VPN's and wide area networks (WANS), Then using access as a file share is not stable.
So if this is only three or four people in a typical office network environment, then depending on the application, it’s very likely that you can simply put the backend on a shared folder on the server and continue to have all the front ends deployed on each computer, and they are linked to that one database backend (mdb) file on the shared folder. MS access quite works well this way.
However for talking about some kind of VPN, or WAN, then one possible solution is to move your back end mdb file to SQL server, and continue to use all the forms,reports etc. from your current application (Most of your application will run as before when you do this).
Another really great technology to consider is thin client, or so called terminal services. Terminal services just a fancy edition of a remote desktop system. TS allows people over even fairly limited bandwidth to run and use the application from remote locations.
However if you’re talking about three to four users on a typical office LAN, that it’s very possible your application will run with very little modifications as is, and you simply move the backend database file to a shared folder on a server somewhere. I can’t stress however that this only works if all the people are on the same little office LAN, And not some kind of remote connection or a WAN/VPN. So, in the case of WAN/VPN, use terminal services, or consider moving the backend to SQL server, and continue to use the application front end as is.
edit - more info:
Ok, so with more info here we can move forward. As mentioned, ms-access is multi-user right out of the box. You need people from different locations to work on this data. So, this means that your application has to be setup for multi-user ability regardless of this different location issue. Once you have the application setup for multi-users then you THEN TACKLE the issue of allowing people to use the software from different locations.
This is no different than if we have something for managing the company Christmas party. If we have a design such that after the Christmas party we delete the whole file to start over for next year’s Christmas party, then we can STILL allow multiple uses into this application. However the design is such that you can’t have multiple Christmas parties active at the same time because of your designs. So in this case it is not the fact that the application is multi user. In this type of scenario, one might actually add a new table called the Christmas party year table. Then one can relate all tables in the application to this master table as a child tables. That way you can have multiple Christmas parties active at the same time for this design. Then when you start the application, users are prompted with some type of list to choose what Christmas party you want to work on.
So don’t confuse the two above separate issues. It doesn’t make sense to ask how terminal services allows the application to be multi user, it does no such thing. What TS does is allow you to take an application that is already multi user and allow people from remote locations to use that application. So TS is a system that allows people to run and use the application from remote locations anywhere on the internet. Your designs will still dictate if your application to allow us to more than one Christmas party to be active at the same time however.
So you don’t make MS access multiuser, MS access is multiuser right out of the box And you don’t have to do anything, except adopt some technologies that let users in different locations use the application. So, that is what TS does, and also SQL server can do for you.
If your design is only allows one project, then we can allow multiple users from different locations in the world to use an application, but they only be allowed to have one active project because of the design limitations of the application.
So all of your table updating logic etc can work as before. YOu simply have to ask is does the applicaion now allow one user to quit the applcaion, and another user enter into it and do their work? Pretend that there's only one standalone computer in the office. Can diffent employees durign the day sit down and use that one computer and that one application with the one backend for each of their separate projects?
So using SQL server, or terminal services doesn’t make your application more (or less) multi user then it is now. These technologies can certainly increase the number of users that can use the application the same time.
So MS access is multi-user now. However what SQL server does, or TS does is allow much more flexibility in terms of how the users remotely connected to this application.
IMO，无论您考虑什么其他解决方案，您肯定需要将数据库转换为多用户服务器。 有一个升迁向导应该很有用（http://support.microsoft.com/kb/237980)，您可能会遇到 Access 允许而 SQL Server 不允许的项目，但大多数情况下应该是轻松的。
您可以将本地访问副本指向使用这个新的数据源（例如通过 ODBC），我相信它应该以几乎相同的方式工作。 已经很多年没有这样做了，不知道这些字段验证规则（它们不是仍然在表单上吗？）会变成什么样子。 您可以下载 SQL Server 的试用版，并在一小时内完成此操作，以了解需要付出多少努力。
IMO you'll definitely need to convert the database to a multi-user server regardless of what other solutions you consider. There's an upsizing wizard that should prove useful (http://support.microsoft.com/kb/237980), you'll probably encounter items that Access allows and SQL server doesn't but for the most part should be painless.
You can point your local access copies to use this new data-source (through ODBC for example) and I believe it should all work pretty much in the same way. Haven't done this in years, no idea what those field validation rules (wouldn't they still be on the form?) would turn out into. You can download a trial copy of SQL server and do this in under an hour to get a feeling of how much effort it would require.
像往常一样，阿尔伯特·卡拉尔（Albert Kallal）为您提供了出色的答案。
如果您想考虑升级到 SQL Server，可以使用 SQL Server 组中的一个工具。
用于访问的 SQL Server 迁移助手（SSMA 访问）
http://www.microsoft.com/sql/solutions/migration/ access/default.mspx 比 Access 升迁向导更好。
另请参阅我对 Microsoft Access Tips 页面上的 SQL Server 升迁的随机想法 http://www .granite.ab.ca/access/sqlserverupsizing.htm
正如您从发布的评论中看到的，您的组织使用的术语“变更控制”是非传统的，而且相当有趣。 尽管我可以看到几年前有人试图找出远程办公室更改数据的解决方案，但会提出这个解决方案。 我也能看出这会是多么令人窒息。
As usual you've got an excellent answer from Albert Kallal.
If you want to consider upsizing to SQL Server there is a tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx that is better than the Access Upsizing Wizard.
Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at http://www.granite.ab.ca/access/sqlserverupsizing.htm
As you can see from the comments to your posting the term change control as your organization uses is non traditional and rather interesting. Although I can see how someone years ago trying to figure out a solution to remote offices changing data would come up with this solution. I can also see though how it would be stifling.