使用Excel每日更新到Access中的现有表 [英] Daily update to existing table in Access with Excel

问题描述

我有一个已建立的资产表，其中包含大约15个字段和200多条记录。每天我从Oracle获得一份包含20个字段和100条记录的Excel报告。目标是在保留一些原始信息的同时，使用任何新信息更新Access表。我确定这是通过更新查询完成的，但我无法让它执行。我上次使用Access的经历是5年多以前在我很少参加的计算机课上，所以我是新手。我已经在我自己的故障排除超过一个星期，任何帮助将非常欢迎和赞赏。

I have an established table of assets that contains about 15 fields and 200+ records. Everyday I get a delivered Excel report from Oracle that contains 20 fields and 100 records. The goal is to have the Access table updated with any new information while maintaining some of its original information. I''m sure this is accomplished with am "Update Query", but I haven''t been able to get it to execute. My last experience with Access was 5+ years ago in a computer class I rarely attended, so I''m a novice. I have been troubleshooting this on my own for over a week, any help would be very welcomed and appreciated.

推荐答案

你是说你得到的所有100条记录每天发送的内容必须输入到Access表中，或者只有在某些新记录尚未存在的情况下才需要添加？

Are you saying that all of the 100 records you get delivered every day have to be input into the Access table, or that only certain new records have to be added if they''re not already there?

某些记录需要更新，但将新信息与旧信息分开并不是很可行。到目前为止，我只使用条件格式化然后手动检查Excel。 （删除重复功能仅在整个记录相同时才有效，而且对我来说并不常见。）是第一步吗？

Some of the records need to be updated, but separating new information from old is not very feasible. So far I''ve only done it with conditional formatting then manually checking in Excel. (The remove duplicates function only works if the entire record is the same, and that doesn''t often happen for me.) Is that step one?

听起来有很多逻辑需要更新哪些记录，需要添加哪些记录以及哪些记录被忽略。我不知道是否可以通过查询来做到这一点。我认为您需要编写一个过程来专门测试指示要采用哪条路径的字段。对于VBA来说并不困难，但我不确定你对它有多放心。如果我更多地了解为什么某些记录会更新而不是单独添加，我可以指出正确的方向。例如你如何知道何时需要更新记录？

It sounds like there is a lot of logic that goes behind which records need to be updated, which ones need to be added, and which ones are ignored. I don''t know if it''s possible to do that with a query. I think you need to write a procedure to specifically test the fields that would indicate which path to take. It''s not that difficult with VBA but I''m not sure how comfortable you are with it. If I knew a little more about why some records are updated vs. added vs. left alone I could point you in the right direction. For example how do you know when a record needs to be updated?

这篇关于使用Excel每日更新到Access中的现有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！