多个位置的 Access 2007 后端 - 共享数据库方法与隔离数据库方法
我正在为两个不同的总部构建数据库方案。每个位置都使用相同的数据方案，但有自己的数据。两者都可以访问同一位置，即放置相应访问后端的共享网络文件夹。两者也将使用相同的前端，其中将根据用户（帐户）显示特定数据。每个位置都有两个主表，每年将增长约 4000-5000 条记录。
大多数时候，每个位置仅使用自己的数据，但大约每周一次，第三方将访问两个位置的一些汇总数据。
在我看来，有两种组织数据的可能性：共享方法，两个位置都使用和共享数据。使用相同的表，其中一个字段指示当前数据属于位置 1 还是 2。我看到的一个巨大问题是通过网络传输的数据量（大约翻倍），因为（到目前为止）据我所知）访问在传输数据后在本地运行选择语句。（我已纠正）。除此之外，每次访问都需要额外的查询。
另一种方法是为第二个位置创建第二组表并将它们保持“分离”（例如将它们重命名为 loc1_tablex 和/或创建第二个后端文件），这也将使备份变得容易。
我更喜欢第二种方法，因为我真的看不出它有什么缺点。我认为它会更快而且更干净。
我是否错过了任何导入内容，或者这种方法是否合理？
编辑：
在进一步讨论该主题后（我们还从客户那里获得了一些新信息），我们决定将数据存储在一个后端和相同的表中。表结构完全相等，我们使用位置 id 来控制哪些数据属于哪个位置（第一种方法）。感谢您对访问后端的一些了解，我在那里缺乏信息，但学到了很多东西！
I'm building a database scheme for two different headquarters. Each location uses the same data-scheme, but has its own data. Both will have access to the same location, a shared network folder where the corresponding access-backend will be placed. Both will also use an equal Front-End, where depending on the user(-account) the specific data will be shown. Each location will have two main tables which will grow by ~4000-5000 records per year.
Most of the time each location only uses its own data, but once every week or so a third party will access some aggregated data from both locations.
In my opinion there are two possibilities to organize the data: a shared approach, where both locations use & work with the same tables, where a field indicates whether the current data belongs to location 1 or 2. A huge problem I see is the amount of data which would be transferred through the network (roughly doubled), because (as far as I know) access runs the select statements locally, after transferring the data. (I stand corrected). Besides that an additional query would be neccessary for each access.
The alternative would be to create a second set of tables for the second location and keep them 'separated' (e.g. renaming them to loc1_tablex and/or create a second backend-file), which also would make a backup easy.
I would prefer the second approach, because I can't really see any downside to it. It would be faster and in my opinion cleaner.
Did I miss anything importing or would this approach be reasonable?
Edit:
After discussing the topic further (we also got some new information from our client), we decided to store the data in one backend and in the same tables. The table structure is fully equal and we are using a location id to control what data belongs to which location (first approach). Ty for shedding some light on the access backend, I had a lack of information there and learned a lot!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如前所述，通过索引，当更多记录被限制在一个位置或另一个位置时，Access 将不会拉取更多记录。
然而，我认为这里将使用 WAN，然后我可能会考虑使用 SQL Server，因为你可以聚合数据，并且这发生在服务器端 - 对于这些类型的场景，假设你会比 JET 快得多需要这样的场景。
这里的另一种可能性是考虑使用 Access 2010 和 Office 365。在这种情况下，您保留常规 Access 前端，但将数据移至 Office 365。
该系统的优点在于您拥有离线数据和本地数据店铺。这意味着 Access 数据库的每个副本上的报告和只读数据的速度将非常高，并且以本地 JET 数据库的速度进行。输入到服务器上的表中的任何新数据都将自动同步到每个人都在使用的客户端。正如所指出的，由于这种自动离线模式，系统运行得非常好，甚至在您的互联网连接中断或中断一段时间后仍能继续运行。
将数据迁移到 Office 365 的唯一缺点是，您必须执行一些额外的步骤来确保引用完整性“正确”迁移到 Office 365。
简而言之，此额外步骤意味着您会破坏（删除）表之间的关系，然后使用新的关系向导来设置与 Office 365 兼容的简单关系。
该系统的优点在于它的启动成本较低，每月 6 美元。您可以将大量用户挂在同一个帐户上。不仅如此，这样的设置还允许您发布和构建 Web 表单以供每个人输入数据，理论上您甚至不必分发访问表单，而是使用 Web 表单。然后使用其中的数据。因此，如果您这样做，则可以在智能手机或 iPad 等设备上查看这些表格和数据。
以下是分步视图，展示了如何打破表之间的关系，然后以正确的方式重新连接它们，以便在 Office 365 上使用：
http://www.youtube.com/watch?v=3wdjYIby_b0&fmt=22&hd=1
最后但并非最不重要的一点是，您可以使用 Office 2010 上传具有完整引用完整性的数据，因此理论上可以使用 access 2007 前端链接到这些表。但是，Access 2007 没有自动脱机模式，并且性能将无法接近使用具有此设置的 Access 2010 时获得的性能。
因此，我认为 Office 365 是一个潜在的理想解决方案，因为您将获得现场任何前端的实时更新。
虽然上面的视频是关于发送数据和使用常规 Access 前端，但您可以在 Office 365 上的 Access 中构建和使用 Web 表单，如以下视频所示：
http://www.youtube.com/watch?v=AU4mH0jPntI
这里不需要 activeX 或 silverlight。
As noted, with indexing, Access will not pull down more records when they are restricted to one location or the other.
However, I think a WAN is going to be used here, then I would likely consider the use of SQL server since you can then aggregate data and this occurs server side – going to be much faster then JET for these types of scenarios, assuming you need such scenarios.
The other possibility here is to consider using Access 2010 and office 365. In this case, you keep your regular Access front end, but move the data up to office 365.
The beauty of this system is you have off-line and a local data store. This means that reporting and read only data on each copy of the access database is going be very high speed and occur at local JET database speed. Any new data entered into the tables on the server will AUTOMATICALLY be synchronized down to those clients that everyone is using. As noted because of this automatic offline mode, then the System Works really great and even continues to work if your Internet connection is broken or interrupted for periods of time.
About the only downside to moving your data up to office 365, is there are a few extra steps you have to do to ensure referential integrity get "correctly" moved up to office 365.
In a nutshell this extra step means you break (remove) the relations between the tables, and then use the new relationships wizard to setup the relationships simple be compatible with office 365.
The beauty of this system is it starts for low cost of $6 per month. And you can hang lots of users off that one account. Not only that, but such a setup would also allow you to publish and build web forms for data entry by everybody, and you could in theory not even have to distribute the access forms, but use web ones. then with the data in it. As such those forms and data could be viewed on something like a smart phone or a iPad if you did this.
Here's a step by step the view that shows how I'd break the relationships between tables, then reconnect them in the correct way for utilizing on office 365:
http://www.youtube.com/watch?v=3wdjYIby_b0&fmt=22&hd=1
Last but not least, you can upload the data with refferential integrity intact using office 2010, and therefore in theory then link to these tables using access 2007 front ends. However access 2007 does not have the automatic offline mode, and performance will not be near close to what you get when using access 2010 with this setup.
So I think office 365 is a potential ideal solution here since you'll get real time updating of any front end out in the field.
While the above video was about sending up data, and using regular Access front end, you can build and use web forms in Access on office 365 as this video shows:
http://www.youtube.com/watch?v=AU4mH0jPntI
No activeX or silverlight is required here.