ADODB SQL语法-Access table内部联接Excel工作表 [英] ADODB SQL Syntax - Access table Inner Join with Excel worksheet

问题描述

我有一个项目，用户需要填写一个Excel文件，然后将数据导出到Access数据库.Excel文件中收集的数据将需要分三步导出:(1)导出数据集1记录，(2)查询访问新导入记录的主键(自动编号)，(3)导出数据设置2记录，其中包括填充为外键的主键.

I have a project where users will need to fill out an Excel file and then export the data to an Access database. The data collected in the Excel file will need to be exported in 3 steps: (1) export data set 1 record, (2) query Access for the primary key (auto-number) of the newly imported record, (3) export data set 2 record, which includes the primary key populated as the foreign key.

我能够通过建立和打开ADODB连接来完成第一步.但是，在第二步中，我需要在Access表和Excel上进行内部联接时遇到麻烦.

I am able to accomplish the first step by establishing and opening an ADODB connection. However, I am running into trouble in the second step where I need to do an inner join on the Access table and the Excel.

wlodb.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\" &userSID & "\Desktop\WLO R&C Database_10-4-16.accdb"

sqlFindREMPK = "Select ID " _
& "FROM [test1] a " _
& "INNER JOIN [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\User\RED-WIP.xlsm].[REM Upload$] b " _
& "ON a.[REM_ID_Database] = b.[REM_ID_Database] " _
& "WHERE (((a.[REM_ID_Database])=""REM9811044""));"

WLOrs.Open sqlFindREMPK, wlodb

ActiveSheet.Range("A10").CopyFromRecordset (WLOrs)

表和工作表名称具有相同的字段名称.问题出在SELECT子句上.如果我按原样离开，则会收到一条错误消息，提示该字段可以引用FROM子句中的多个表.如果我添加表名称，例如[test1].[ID]，那么我将收到一条消息，提示未为必需的参数提供任何值.如果我将Excel字段名称稍微更改为ID1，并将SELECT子句保留为ID，则代码运行良好.

The table and the worksheet name have the same field names. The problem is with the SELECT clause. If I leave as is, I will get an error saying the field could refer to more than one table in the FROM clause. If I add the table name such as [test1].[ID] then I will get the message saying no value given for required parameters. If I change the Excel field name slightly to ID1 and leave the SELECT clause as just ID the code runs fine.

推荐答案

为表提供别名后，在引用该表时必须使用该别名.您无法再通过其原始名称访问该表.

Once you provide an alias for a table, you have to use that alias when referring to that table. You cannot access the table by its original name any more.

sqlFindREMPK = "Select a.ID " _
  & "FROM [test1] a " _
  & "INNER JOIN [Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\User\RED-WIP.xlsm].[REM Upload$] b " _
  & "ON a.[REM_ID_Database] = b.[REM_ID_Database] " _
  & "WHERE (((a.[REM_ID_Database])=""REM9811044""));"

这篇关于ADODB SQL语法-Access table内部联接Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！