Can an Excel Data Connection lock a DB table?

Question

Hi,

If I add a simple Sql Server table data connection to an Excel worksheet, can it lock that table if the connection is never closed?

Answer 1

The usual answer: yes, but it depends on the cicumstances ...

.. mostly on what locking strategy your database employs (again depending on the configuration). My assumption is that Excel would open a "regular" connection to the database. If you now query a table, that may already lock the table if you have pessimistic locking strategy.

Hope this helps .. there is tons of information out there on locking as related to a lot of database systems.

Answer 2

Though I think it's possible to lock a table, I'd say that Excel generally wouldn't, since you're only selecting data. If you try to update something, sure you'll lock the underlying table, but as long as you're only viewing data, you should be fine.

To make sure, why not just do some testing on a less-user table in the system, or maybe a table in another DB that's configured the same way? Then you can use SP_LOCK on the SQL Server to see if you Excel connection has locked the table, or you can try to execute another query against your table to see if it gets blocked.