Save DB connection and query within spreadsheet

[Doug]

Hi. I created a web form that dumps the data into a SQL server database.
This form is for three, not so technical co-workers to use and need to make
retrieving new data as simple as possible. The users would like to use Excel
to retreive the data.

Is there any way to imbed SQL server access from within Excel so that all
the user has to do is open the spreadsheet and click refresh data?

I've been playing around with it with DSN's and dbq files and can easily
make it work myself, but I need to simplify the process. If I exit out of
the spreadsheet, and go back in, I need to reestablish my connection each
time. Is there a way around this so that the query and DB connection are
embeded within the spreadsheet?

Thanks
Doug

 

[Jim]

Doug,
After I get a query working the way I want, I record it as a macro,
then create a click button and copy the code to a click button
procedure.

To record your macro, select a cell within your query, start the macro
recorder, then select from menus, data, get external data, edit query.

Bring up the editor then exit to excel and stop the recorder. Open the
VBE and look at the project manager. You will find the code for your
macro in a new module.

You can go back to your spreadsheet and add a click button then paste
your macro code in the procedure for you click button and save your
sheet.

In the future your query will run from the click button.

 

[VancitysFinest]

Start with a DQY query and edit the 3rd line that has the database
connection info.

Here is an example, the driver name is from the list when you add a DSN
in ODBC setup in the Control Panel.

DRIVER={ORACLE ODBC
DRIVER};SERVER=YOURSERVERNAME;UID=DB_LOGIN_ID;PWD=DB_PASSWORD;DBQ=YOURSERVERNAME

Save that, then on your spreadsheet import data and select this DBQ
query you just made, making sure you check "Save Password". You'll
never be bothered for connection settings again.