Quote:
Originally Posted by matpj
I usually create a query by creating an appropriate DSN in my control panel, then choosing that MSN from MSQuery when it fires up in Excel.
The data is then returned to the sheets etc etc.
This means that each user that wants to run the spreadsheet and refresh data needs to have the correct DSN set up (same name)
Is there an alternative to this, so that end users do not need the DSN created? They can just open the spreadsheet and refresh..
|
Hi Matt -
Does MSQuery let you specify ADO.NET connection strings? If so, you can use them to give a full connection string, rather than just an ODBC DSN - you can get details of the connection string you would need from
www.connectionstrings.com. Then your users wouldn't need to create the DSN on their machine.
If it doesn't let you use ADO.NET data providers, you could try building the spreadsheet using a product that I work on, Resolver One (
www.resolversystems.com). It's scripted with IronPython, which means you can use all of the .NET APIs. So for example, you could enter the code as:
from System.Data.SqlClient import SqlConnection
connection = SqlConnection('Data Source=database server; Initial Catalog=db; other connection string details...)
connection.Open()
command = connection.CreateCommand()
command.CommandText = 'select * from table' # your SQL statement here...
reader = command.ExecuteReader()
# and then add code to populate the worksheet from the reader...
Then you could have formulae in the spreadsheet that use the data pulled in from the database.