Using INSERT INTO to populate/append a linked excel file

[Sharonann2021]

Good morning. I am a fresh newby here and somewhat new to Access 2010. I have a simple database that allows for updating my table known as InventoryItems. Each time I or someone else updates and adds new inventory via a form to this table, I have a button that automatically saves the info, and then sends a report as an excel file of the current record with a date and time stamp. That file is then used to import that record into my inventory file in Quickbooks.



What I am trying to do is rather than importing files in Quickbooks over and over again throughout the day, I have linked an excel file as a table into the database. However, when I attempt to use the following code

CurrentDb.Execute _
"INSERT INTO Updates SELECT * FROM InventoryItems Where [Item] = " & Me.[Item] & ";"

I get a Run-time error '3134': Syntax error in INSERT INTO statement.

I tested this code using an access table rather than a linked table and it worked just fine. However, I really need this info output/appending an excel file.

Any help would be greatly appreciated.

[isladogs]

Deleted. Duplicate post

[isladogs]

Linked Excel tables are read only and cannot be updated. This situation is due to a legal dispute over copyright dating back to 2002 following which MS removed the ability to edit linked Excel files from Access. There are ways around it including
1. Using Excel automation
2. Exporting the new data to Excel using DoCmd.TransferSpreadsheet. and overwriting the file...
3. Creating a query based on the external Excel file ...though that only works with specific connection info.

See also the first item in the Similar Threads below

[Sharonann2021]

Thank you for your quick reply! That explains it. At least now I can pursue another avenue.

[Gicu]

Unfortunately editing a linked Excel file in Access is no longer possible, there are many references to this on the web, here are a few:
https://www.utteraccess.com/topics/2007113
https://www.pcreview.co.uk/threads/i...table.3161070/


So I think you should create an Access table that replicates the Excel file, fill it up with your new data throughout the day then export it to Excel using Docmd.TransferSpreadsheet to create\replace the source file you currently have linked.
Another option is to use automation to add each record to your existing Excel file, this link should get you started:
https://docs.microsoft.com/en-US/off...-changing-data

Cheers,