通知 Excel 2007 有关 SQL Server 2008 R2 中数据库的更改
我在将 Excel 2007 与 SQL Server 2008 R2 一起使用时遇到很多麻烦。
我需要在两个或多个工作簿之间共享数据，并且我想通过远程服务器上的 SQL 数据库（放置在 SQL Server 2008 R2 中）来完成此操作。我将 VBA 与 ADO 和 SQL Server Native Client OLE DB 提供程序一起用作数据访问提供程序。当一个客户端将数据从 Excel 上传到数据库时，我希望 SQL Server 2008 R2 向其他工作簿发出一个信号，告知数据库已更改，我的问题是如何做到这一点？我可以用 VBA 来做吗？
我已经弄清楚如何在 SQL Server 2008 R2 中使用事件通知，但我的问题又是如何通知 Excel，而不运行每隔 30 秒左右在 Service Broker 队列中查询消息的宏。据我所知，Excel 2007 不支持多线程，并且由于我还需要 Excel 来运行其他宏，因此每 30 秒运行一次这些检查并不是一个解决方案。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
实现这一点的方法实际上是通过 Excel。虽然用户/事件调用的 VBA 是单线程的，但
Application.OnTime在独立线程中运行。如果您希望即使在工作簿关闭时也能执行此操作，请考虑在客户端使用服务。与引导 SQL Server 关注客户端工作簿相比，从 Excel 向 SQL Server 发出计时器调用要简单得多，成本也低得多。
The way to do this is in fact via Excel. While user/event invoked VBA is single-threaded,
Application.OnTimeruns 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.