INSERT data into Access table from excel-linked table

Question

I have an Excel linked table and I want to run procedure to add missing data from the linked table to the Access table

I'm getting error

Run-time error '3075':

Syntax error (comma) in query expression

'(Lot,Status,STARTDATE,CLOSINGDATE,Type,ADDRESS)'.

when I click "Debug" - the line "DoCmd.RunSQL SQL" -----is highlighted

Private Sub Form_Load()

Dim SQL As String

SQL = "INSERT INTO Job\_Sheet\_updated (Lot,Status,STARTDATE,CLOSINGDATE,Type,ADDRESS) " & \_ 

      "SELECT (Lot,Status,STARTDATE,CLOSINGDATE,Type,ADDRESS) FROM Job\_Sheet " & \_ 

      "WHERE NOT EXISTS " & \_ 

      "(SELECT \* " & \_ 

      "FROM Job\_Sheet\_updated " & \_ 

      "WHERE Job\_Sheet\_updated.Lot = Job\_Sheet.Lot);" 

 DoCmd.RunSQL SQL 

End Sub

Answer 1

SQL = "INSERT INTO Job_Sheet_updated (Lot,Status,STARTDATE,CLOSINGDATE,Type,ADDRESS) " & _

"SELECT (Lot,Status,STARTDATE,CLOSINGDATE,Type,ADDRESS) FROM Job_Sheet " & _

"WHERE NOT EXISTS " & _

"(SELECT * " & _

"FROM Job_Sheet_updated " & _

"WHERE Job_Sheet_updated.Lot = Job_Sheet.Lot);"

debug.print sql

DoCmd.RunSQL SQL

This way,you can copy from immediate window and paste to new query sql view which will show error details.

If possible share some datas which can paste to Access and your expected reault.

Answer 2

after some simple example testing in a sandbox Access database, I found out the 1st SELECT statement should not have (). thanks

Answer 3

Thanks for your feedback.

You are welcome.