Using VB .NET 2005 to create an Excel 2000 pivot table with a connection to SQLEXPRESS
Has anybody tried this when connecting to a user instance of SQLEXPRESS? I have tried every combination of connection strings I can think of and both the SQL Native Client driver and the SQL Server driver. The error says there is an invalid connection string attribute. I am able to attach the mdf file directly to the SQLEXPRESS instance by using the following connection string in VB.NET:
"Data Source=.\SQLEXPRESS;AttachDbFilename="
&Full_MDF_file_pathway& _";Integrated Security=True;Connect Timeout=60;User Instance=true;"Here is my best guess for the ODBC connection required by Excel. I have tried opening a connection as above prior to using the xlSheet.QueryTables.Add method but it makes no difference.
Dim strConnect As String = "ODBC;DRIVER=SQL Server;SERVER=.\SQLEXPRESS;Integrated Security=True;D
ATABASE=" & Full_MDF_file_pathway & ";"Dim xlQueryTableNotesData As Excel.QueryTable
xlQueryTableNotesData = xlSheet.QueryTables.Add(strScenarioODBCDataSource, xlRange, strSQLnotes)The main problem seems to be that the database is not attached to the SQLEXPRESS intance. I know it isn't because I can't see it in SQL Server Management Studio Express. The database has been created on the fly by VB .NET and I just attach using the AttachDbFilename keyword in the connection string. This has been fine until I try to create the Excel pivot.
Any suggestions or alternative strategies?
