Where to store DB connection string for Excel 2007 Template App Connection to SQL Server Database?

Question

I have a legacy Excel 2007 Template that has hardcoded Ms Access MDB path in the VBA code used to connect to the Access tables and save, retrieve data.

I migrated the MS Access Database over to SQL Server with Integrated Authentication for the Excel 2007 Template Users.

My question is, What is the Recommend Way / Best Practice for storing the SQL Server DB connection string and retreiving it in Excel 2007 VBA to save and retrieve data?

In the past, I have done the following.

1. Use a Registry Key setting that has the Connection String. Then in the VBA, write a function that reads the registry key and returns the connection string.

2. Have a "Settings" hidden sheet within the Excel Template, with named cell for the connection string. Read the connection string in VBA by accessing that named range.

3. Use a .INI txt file that goes with the Excel template. (This is not ideal and I want to avoid this as it builds a dependency on that external file)

I don't like # 1 because I want to avoid writing to/reading from Registry if possible. # 2 feels ok, thought I am not sure if there is a better "cleaner" way for doing this.

Any thoughts?

thanks

-Shiva

http://mycodetrip.com/

Answer 1

How about storing it under CustomDocumentProperties?

Note: I am not sure, if the workbook (based on a given template) will inherit the property defined using CustomDocumentProperties in the template.