Notifying Excel 2007 about changes in a database in SQL Server 2008 R2

I have a lot of trouble using Excel 2007 together with SQL server 2008 R2.

I need to share data between two or more workbooks, and I want to do this through a SQL database (placed in SQL Server 2008 R2) on a remote server. I use VBA with ADO and SQL Server Native Client OLE DB provider as data access provider. When one client have uploaded data from Excel to the database, I want SQL Server 2008 R2 to fire a signal to the other workbooks about that the database has been changed, and my q开发者_C百科uestion is how to do that? And can I do it in VBA?

I have figured out how to use event notification in SQL Server 2008 R2, but again my problem is how to notify Excel without running a macro that query for messages in a Service Broker queue every 30th second, or so. As well as I know Excel 2007 doesn’t support multithreading, and as I need Excel to run other macros as well, it isn’t a solution to run these checks every 30th second.

The way to do this is in fact via Excel. While user/event invoked VBA is single-threaded, Application.OnTime runs in an independent thread. If you want it to take place even when the workbook is closed, consider using a service on the client side.

Firing off a timer call from Excel to a SQL server is far simpler and less expensive than coaxing a SQL Server to pay attention to client workbooks.