用于删除和创建在子表中添加相同 ID 记录的关系的 VBA 代码，是否可能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9774802/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA code for deleting and creating relationships adding same ID records in the child table, is it possible?
提问by giovetti
I have a table that I create every time with updated data from a main database. so there is a MAIN database with all the data, from that I query and get a dataset which I save as a table lets call it (TABLE1), in a small access database. The MAIN database gets updated manually by me and some other colleagues and then I delete the old table on my access database and create a new one (TABLE1), with the updated data. The updated data represents new records, and some updated fields on existing records.
我有一个表，我每次都使用主数据库中的更新数据创建该表。所以有一个包含所有数据的主数据库，我从中查询并获取一个数据集，我将其另存为表，在一个小型访问数据库中调用它（TABLE1）。MAIN 数据库由我和其他一些同事手动更新，然后我删除了访问数据库中的旧表，并使用更新后的数据创建了一个新表 (TABLE1)。更新的数据代表新记录，以及现有记录上的一些更新字段。
I have another table (TABLE2), on my access database with some extra data related to the records in (TABLE1). Every time I do this I have to break up the relationship link I have within the tables ID fields via VBA. Then I have to add empty rows in TABLE2 to match TABLE1 and recreate the relationship. Can it be done better with VBA code?
我有另一个表 (TABLE2)，在我的访问数据库上有一些与 (TABLE1) 中的记录相关的额外数据。每次我这样做时，我都必须通过 VBA 打破我在表 ID 字段中的关系链接。然后我必须在 TABLE2 中添加空行以匹配 TABLE1 并重新创建关系。用 VBA 代码可以做得更好吗？
Example:
例子：
TABLE1
-------
name
-------
'cat'
'dog'
'mouse'
TABLE2
------
cost
------
23
13
25
Query based oon ID fields (autonumber)
基于 ID 字段的查询（自动编号）
-------|-----
name |cost
-------|------
'cat' |23
'dog' |13
'mouse'|25
回答by HansUp
You should not need to delete TABLE1 so that you can then recreate it with a fresh set of data. Instead, discard the existing rows ...
您不需要删除 TABLE1，以便随后可以使用一组新数据重新创建它。相反，丢弃现有的行......
DELETE FROM TABLE1;
... then append your new rows to it ...
...然后将您的新行附加到它...
INSERT INTO TABLE1 (
fld1,
fld2,
etc
)
SELECT
fld1,
fld2,
etc
FROM MAIN
WHERE <the condition which identifies the new records>;
However I'm unsure how that applies to the existing relationship. If you're not enforcing relational integrity, that may be enough.
但是我不确定这如何适用于现有的关系。如果您不强制执行关系完整性，那可能就足够了。
If you still need to delete the relationship, you can remove it from your database's Relations collection.
如果您仍需要删除关系，可以将其从数据库的关系集合中删除。
Dim db As DAO.Database
Set db = CurrentDb
db.Relations.Delete "YourRelationName"
Set db = Nothing
Later, to recreate the relationship you can use the Database.CreateRelation Method. That will likely be more involved than deleting the relationship. See this detailed example: VBA code for creating MS Access Relations
稍后，要重新创建关系，您可以使用Database.CreateRelation Method。这可能比删除关系更复杂。请参阅此详细示例：用于创建 MS 访问关系的 VBA 代码
