CODEWITHSUNDEEP
excelms-accessvbams-access-2010
Carl
Carl

Reputation: 5779

Executing SQL updates on linked Excel table in Access

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?

Upvotes: 3

Views: 2083

Answers (2)

Lance
Lance

Reputation: 1

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.

Upvotes: 0

HansUp
HansUp

Reputation: 97131

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.

Upvotes: 3

Related Questions