Import from Excel: Question for Jamie Collins

[Ralph Rotten]

Access 97: I want to import from Excel 97 into an existing table. I've tried
following your examples as posted, but keep getting a syntax error (3134).
What am I doing incorrect?

Private Sub Import_Excel_Click()

DoCmd.RunSQL ("INSERT INTO Test_Table (TRANS_CODE, ORDER, SHIP_LOC,
QTY_SHIP, SHIP_DATE, ITEM_ID, UOM, EXT_COST) " & _
"SELECT TRANS_CODE, ORDER, SHIP_LOC, QTY_SHIP, " & _
SHIP_DATE, ITEM_ID, UOM, EXT_COST FROM " & _
"[Excel 8.0;HDR=YES;Database=C:\1 My Documents\MASTER
DATABASE\WC42.xls;].[ORDERS$];")

End Sub

1) My Excel column headers are the same as my Access table column names.
2) "ORDERS" is the name of the one and only worksheet in the workbook.
3) Tried with and without "Database=" in the FROM clause.

Thank You.

 

[Jamie Collins]

keep getting a syntax error (3134).
What am I doing incorrect?

DoCmd.RunSQL ("INSERT INTO Test_Table (TRANS_CODE, ORDER, SHIP_LOC,
QTY_SHIP, SHIP_DATE, ITEM_ID, UOM, EXT_COST) " & _
"SELECT TRANS_CODE, ORDER, SHIP_LOC, QTY_SHIP, " & _
SHIP_DATE, ITEM_ID, UOM, EXT_COST FROM " & _
"[Excel 8.0;HDR=YES;Database=C:\1 My Documents\MASTER
DATABASE\WC42.xls;].[ORDERS$];")

ORDER is a reserved word in SQL (as in ORDER BY). If you put this word
in brackets i.e. [ORDER] the query should run as posted (in code, you
need a double quote before the second SHIP_DATE but this will give a
compile error - though not in the Immediate Window!).

Jamie.

--

 

[Ralph Rotten]

Thanks!