SQL in Excel

[Neil Holden]

I am trying to create code to remember the SQL Username and Password so that
when all users connect to my excel workbook its connects to the SQL without
have to type in the username and password everytime.

I am using Excel 2003 SP2 and I have created many queries that are linked to
a live SQL Database, but when a standard user wants to open the excel sheet
it won't allow them to connect.

Please help this is proving to be a nightmare..

 

[Ed Ferrero]

Hi Neil,

I am trying to create code to remember the SQL Username and Password so
that
when all users connect to my excel workbook its connects to the SQL
without
have to type in the username and password everytime.

I am using Excel 2003 SP2 and I have created many queries that are linked
to
a live SQL Database, but when a standard user wants to open the excel
sheet
it won't allow them to connect.

Please help this is proving to be a nightmare..

Not a good idea to store the username and password in a workbook - not
secure.
I suppose you could get away with it in a corporate environment.

Sample code to refresh an Excel query from SQL Server follows.
Carl Prothman keeps a fairly complete collection of connection strings here
http://www.carlprothman.net/Default.aspx?tabid=81

Dim strConn as String
Dim strUsr as String
Dim strPW as String
Dim strDSN as String
Dim strDB as String

' suggest you get these from MsgBox
strUsr = "xxx"
strPW = "yyy"

strDSN = "myDSN"
strDB = "myDatabase"

strConn = "ODBC;DSN=" & strDSN
strConn = strConn & ";UID=" & strUsr
strConn = strConn & ";PWD=" & strPW
strConn = strConn & ";APP=Microsoft Office 2000;DATABASE=" & strDB

With Worksheets("mySheetName").Range("myRangeName").QueryTable
.Connection = strConn
.CommandText = "SELECT * FROM myView"
.Refresh BackgroundQuery:=False
End With

Ed Ferrero
www.edferrero.com