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 question 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.
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 question 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.
