Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SQL in Excel
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.. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SQL in Excel
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").Que ryTable .Connection = strConn .CommandText = "SELECT * FROM myView" .Refresh BackgroundQuery:=False End With Ed Ferrero www.edferrero.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|