Dynamic Database Connection - EXCEL to SQL Server

Hi *, is there a way to connect Excel to SQL Server so that Excel serves as a frontend to SQL Server? I heard rumors that this is possible with the 2007 release.

I'm looking for a product that helps me storing massive data outside an Excel file. Right now, I'm using Palo (open source multidimensional database).

Regards,
Steve

Are you looking for something along the lines described in this article: http://support.microsoft.com/kb/321686 titled "How to import data from Excel to SQL Server"?

|||Well, something like that... Basically, I wanna use Excel as a Front-End to SQL Server. That means, data input AND output in Excel. Data storage in SQL Server.

Any ideas?|||

What about using ADODB.Connection in VBA. That way you can use Excel as your interface and store data in SQL Server.

Jakob

|||

Visual Studio Tools for Office focuses on the scenario that you describe...you can find a good article that walks through the same basic scenario at: http://msdn2.microsoft.com/en-us/library/aa192473(office.11).aspx

If you can't use Visual Studio Tools for Office, you could potentially use a poor man's version of what it does under the covers. By adding an "Update" column and using the code in the link that Peter provided earlier, you could determine what rows need to be updated and execute an appropriate query.

If you aren't bound to the unique functionalities of Excel, a linked server in Access might be an easier way to provide the basic reporting/editing functionality that you are looking for.