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
.NET Development36
|