ADODB SQL Syntax - Access table Inner Join with Excel worksheet

Question

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.

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)

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.

That worked. I didn't think about using an alias before declaring it in the FROM clause. Seems a bit counterintuitive, but it worked. — Ryan
– Ryan, Commented Oct 16, 2016 at 12:01

knittl · Accepted Answer · 2017-07-05 17:40:27Z

1

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""));"

answered Jul 5, 2017 at 17:40

knittl

269k59 gold badges339 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Parfait Over a year ago

Alternatively, we could have bumped @sgeddes to give this old question a new answer!

Collectives™ on Stack Overflow

ADODB SQL Syntax - Access table Inner Join with Excel worksheet

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related