Using Excel 2010 using VBA, I would like to be able to start/stop and view running SQL Server jobs, create new tables for import jobs, as well as run backups and other administrative tasks on my local SQL Server. I would also like to use it as a front end for reports for my imported data.
I am trying to locate a reference to SQL SMO but do not see it in Excel Tools -> References. It is installed, I have verified that.
My development environment is:
I am aware that this could be done a dozen other ways but I have been trying to get it to work in Excel for days and searching on this combination of programming has yielded nothing except how to do this with SQL DMO (the former object model) which I do not have installed as its deprecated in SQL Server 2008 I understand.
I also tried a direct reference to the Microsoft.SqlServer.Smo.dll file located at C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies which failed with a Cant add a reference to the specified file. error.
Is it possible to gain access to SQL SMO via VBA in Excel or am I out of luck and should move to VB.net / C# etc?
After much research I have found out that it is not possible to consume what is essentially a dot-net, 64-bit, resource from a 32-bit Excel program. I would have to install the 64-bit version of office and give that a try. Perhaps in a VM I shall try.