Executing SQL updates on linked Excel table in Access

Question

Using Access 2010, I imported several Excel tables into Access, and built forms that allowed the user to update and add information to the tables. The forms work by dynamically generating SQL alter and update statements in VBA and then running DoCmd.RunSQL. However, I wanted the changes to the tables to be reflected in the original Excel tables so I deleted the tables in Access, and instead linked the Excel tables to Access.

All of my select queries continue to work so I am able to access the data in essentially the same way, but I am not able to execute SQL statements on the tables in VBA using DoCmd.RunSQL, which means I can't perform the SQL update and alter statements like I did before.

My question is: can I execute SQL including alter and update statements on my linked Excel tables, and if so, how?

AFAIK, the only way around this to edit the Excel file directly. I think you can do this through ADO or coding directly into the Excel file through VBA (called from Access) — Scott Holtzman
– Scott Holtzman, Commented Jan 21, 2016 at 19:09

HansUp · Accepted Answer · 2016-01-21 19:56:36Z

3

can I execute SQL including alter and update statements on my linked Excel tables?

No, the linked worksheets are read-only from Access' perspective. That is not just a limitation for Access SQL. If you open one of those links directly in Datasheet View, you can not alter stored values, delete rows, or add new rows.

Microsoft explains ...

Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook.

That edit capability was removed with Access 2002 and 2003, and not added back to subsequent Access versions.

See the linked page for further details, including their suggested workaround.

edited Jan 21, 2016 at 19:56

answered Jan 21, 2016 at 19:45

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

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

2 Comments

Carl Over a year ago

Thanks, I came across that link, but was hoping that it didn't apply to Access 2010. Oh well

HansUp Over a year ago

Yeah, it sort of reads like it might apply only to 2002 and 2003, but not so. AFAIR, there was an intellectual property dispute and MS had to disable that capability in response. I've not seen any suggestion the situation will ever change. :-(

Lance · Accepted Answer · 2016-01-22 01:16:12Z

0

You have an easy option. If you link the Excel file(s), you can then create a make table query using the linked Excel table. After the new table is created, you now have Access data which you can manipulate to your liking. You can then export new, updated, or changed data it back to Excel if needed.

Keep in mind, when working with external data, you need to get a little more exotic in your methods to access and manipulate that data.

answered Jan 22, 2016 at 1:16

Lance

12 bronze badges

Collectives™ on Stack Overflow

Executing SQL updates on linked Excel table in Access

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related