Using VB .NET 2005 to create an Excel 2000 pivot table with a connection to SQLEXPRESS  
Author Message
sql_chris_nz





PostPosted: .NET Base Class Library, Using VB .NET 2005 to create an Excel 2000 pivot table with a connection to SQLEXPRESS Top

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;DATABASE=" & 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



.NET Development36