比较两个表的字段名称 [英] Comparing field names of two tables

问题描述

我花了几个小时寻找类似的问题。虽然有一些回复，我找不到任何点。


所以基本上，我有两张桌子。一个是标准访问表（table1），另一个是从Excel导入的表（table2）。现在table2有表1的字段名称，还有一些我不需要的额外字段。现在我希望Access比较这两个表并将表2中可以匹配的任何字段的内容复制到表1中。


i已尝试过INFORMATION_SCHEMA.COLUMNS，但代码没有似乎认识到这个表达。


任何人都可以提出一些想法！如果需要可以提供更多细节。


非常感谢..


saran

解决方案

要将数据从[FromTable]复制到[ToTable]，但只有在字段名匹配的地方，您才能在代码中建立一个SQL字符串，然后您可以执行该字符串。


APPEND查询的SQL字符串的基本模板是：

展开 | 选择 | Wrap | 行号

感谢您的回复@NeoPa。我制定了追加查询，但存在一个问题。


table1 = MLE_Table

table2 = tbl_Import


现在，table1是一个标准表，就像一个主表。每次用户上传新文件时，table2都会更改。所以表2中的信息必须进入table1，table1不断增长。现在的问题是table2是用户上传的，因此有时可能会丢失一个字段，如果是这个场景，代码必须复制其他字段并在table1中将此字段留空。这是INSERT INTO语句似乎不起作用的地方。如果缺少其中一个字段，那么整个SQL都不起作用。


这就是为什么我认为我需要在每次上传新文件时比较字段名称。请帮我解决一下这个。这是我正在使用的代码

展开 | 选择 | Wrap | 行号

这有几个问题。

1. 您发布的代码从未在VBA中编译过。它完全无效。这很重要。
   
   请参阅发布前（VBA或SQL）代码。如果我要帮助你，你需要确保遵循这些指示。我没有时间指出编译器已经告诉你的所有问题。如果你对它告诉你的东西感到挣扎，我会这么做，但你必须至少先做这些基础知识。
2. 请注意我在早期产品中发布的代码是SQL代码。这也很重要。
   
   说明是使用两个表中字段的字段名来构建字符串，然后执行它。您的VBA代码中没有任何内容表明您已理解这一点，因此我将尝试使该过程更加清晰。

展开 | 选择 | Wrap | 行号

I have spent a few hours searching for a similar Problem to mine. though there are a few replies, i could not find any to the Point.

so basically, i have two tables. one is a Standard Access table (table1) and the other is a table imported from Excel (table2). now table2 has the field names of table 1 and also a few other extra fields that i dont Need. now i want Access to compare These two tables and copy into table 1 the contents of whichever fields that it can match from table 2.

i have tried INFORMATION_SCHEMA.COLUMNS, but the code does not seem to recognise this Expression.

can anyone suggest some ideas please!!! can give more Details if required.

Thanks a lot..

saran

解决方案

To copy data from [FromTable] into [ToTable] but only where the fieldnames match you can build up a SQL string in your code which you can then execute.

The basic template of the SQL string for an APPEND query is :

Expand|Select|Wrap|Line Numbers

Thank you for your reply @NeoPa. I formulated the append query but there is an issue.

table1 = MLE_Table
table2 = tbl_Import

now, table1 is a Standard table like a master table. table2 changes everytime the user Uploads a new file. so the Information from table 2 has to go into table1 and table1 keeps growing. now the Problem is that table2 is user uploaded and therefore it can be that sometimes a field might be missing in case of this Scenario the code must copy the other fields and leave this field blank in table1. this is where the INSERT INTO Statement does not seem to be working. if one of the fields is missing, then the whole SQL does not work.

thats why i think i Need to compare the field names everytime a new file is uploaded. please help me with this. this is the code that i am using

Expand|Select|Wrap|Line Numbers

There are a couple of problems with this.

1. The code you''ve posted has never compiled in VBA. It''s just totally invalid. This is important.
   See Before Posting (VBA or SQL) Code. If I''m to help you at all you need to make sure you follow those instructions. I don''t have time to point out all the problems the compiler can already tell you about. I will if you struggle even with what it tells you, but you must do those basics first at least.
2. Please notice that the code I posted in my earlier offering was SQL code. This is also important.
   The instructions were to build up the string using the field names of the fields from both tables and then execute it. There is nothing in your VBA code that indicates you''ve understood that, so I''ll try to make the process a little clearer.

Expand|Select|Wrap|Line Numbers

这篇关于比较两个表的字段名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！