looking for advice for the best way to view information from an sql database.
i need the info to be refreshed everytime its open. the excel sheet will be stored on a sharepoint so that anytime anyone opens it the table refreshes with the data.
thanks for the reply. ill try give more detailed information as what i want to do.
Data is entered in a website and stored in a MS SQL Server.
I want to access this data (i have the connection string and passwords) and show in an excel sheet so that anyone on my network can view it and add comments to the data in the excel file.
when the excel file is opened, i want the data to refresh so the latest data can be seen
You should be able to setup an "import" over OLEDB or however you want to connect. There's nothing special about it being in Sharepoint other than it might complain about rights to modify the excel sheet depending on how you setup permissions.
ok thanks but when i try to import using 'from other sources.. from data connection wizard (import data for an unlisted format by using the data connection wizard and OLEDB).. then click Microsoft SQL Server.. then i enter the server name (and username and password).. then select the database and table.. then hit finish.....
the connection file is stored on my C drive??
if i want other people to be able to open this and refresh the data, it wont work because it will reference my C drive?
I see now. 2007 puts your balls in a vice and tries to force you to never embed any data connection information.
Here's a way around it: Create the connection file and save it like you normally would. Then, click "connections", highlight the connection you created and click "properties.
Switch over to the "definition" tab. Save the connection string in notepad or something, then type some random text into the connection string box and click "ok".
It will yell at you that the connection string is no longer identical to the file and will be stored in the spreadsheet. Obviously this is acceptable. This will clear the connection file association and you are then free to paste in the correct connection string again.