All geek questions in one place

SQL Syntax ADODB - Access Table Internal Record with Excel Worksheet

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 must be exported in three stages: (1) data export data set 1, (2) primary key access request (automatic number) of the new imported record, (3) set 2 record export data, which includes a primary key filled in as a foreign key.

I can complete the first step by establishing and opening an ADODB connection. However, I ran into difficulties in the second stage, when I needed to make an internal join in an Access table and 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, .

+4
1

, , . .

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

Source: https://habr.com/ru/post/1657833/

More articles:


All Articles