比较excel和访问表并更新更改 [英] compare excel and access table and update changes
问题描述
我有一个主访问表，我们存储所有员工信息。我在excel中开发了各种应用程序，它们在master中导入和使用信息。
现在我想开发一个excel应用程序，它将从访问表或某些特定数据中导入所有记录（已定义）在excel电子表格中按字段ID）。 （***成功完成）
当用户关闭文件时，用户将能够查看记录并对电子表格进行更改excel将询问是否需要更新记录主人，如果用户选择是，我需要一个VBA代码来运行检查，这将检查主人与电子表格并更新记录。
我知道我可以链接excel电子表格和这很容易做到，但我们通过我们的网络在我们的办公室使用它，大约有50人可能需要立即更新它。现在excel电子表格无法一次性打开和更新。
如果我有办法可以用来做这个，请告诉我。
Access Master Records有员工信息。如EMPID，名称，经理名称，加入日期，指定，电子邮件。
I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master.
Now I want to develop a excel application that will import all the records from access table or some particular data (defined by field id) in excel spreadsheet. (***successfully acomplished)
User would be able to view the records and make changes to the spreadsheet now when the user closes the file excel will ask if records needs to be updated to the master, if user selects yes I need a VBA code to run through a check which will compare the master with spreadsheet and update the records.
I know I can link the excel spreadsheet and do this easily, however we use this in our office through our network and there are about 50 people who could need to update it at once. Now excel spreadsheet cannot be opened and updated by all at once.
Please let me know if there is a method through which I can use to do this.
Access Master Records have employee info. like EMPID, Name, Manager Name, Date of Joining, Designation, Email.
推荐答案
这听起来像一个复杂的设置。我会建议不要使用这样的一个，因为涉及太多的复杂性，例如当许多实例中存在多个复制时要保持更新。
也就是说，最简单，最可靠的显然不是一个简单的Excel过程。 Excel部件可以导出要导入的Access部件的文件。它甚至可以通过打开数据库来触发Access代码。在主表和Excel表中包含日期/时间是明智的，因此只使用最新的。显然，只有更新的记录应该包含在导出中。
This sounds like a complicated setup. I would advise against using one such as this as there are so many complications involved, such as which update to keep when there is more than one from the many instances.
That said, the easiest and most reliable would clearly not be a simple Excel process. The Excel part could export a file for the Access part to import. It could even trigger the Access code to start by opening the database. It would be sensible to include the date/time in both the master and the Excel tables so only the latest would be used. Clearly only the updated records should be included in the exports.
我能够实现一点点......
I was able to achieve little bit...
我会提出一些建议。
- 始终明确声明您的程序为Public或Private（第1行）。
- 尽可能明确声明变量（第2行和第3行）。
- 避免对集合使用带编号的索引（第4行）。它更难以阅读，不太明显代码正在做什么。
- 使用缩进。这有助于读者理解您的代码正在做什么。
以下代码的修订版本用于说明这些要点：
A revised version of your code below to illustrate these points :
这篇关于比较excel和访问表并更新更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
