Hi everyone
I'm using ADO 2.8 to connect to a SQL database to run a query, and I'm
using a file DSN - most of my users don't have permission to create
ODBC datasources and this gets round that.
My code includes a connection string:
With AdoConn
.ConnectionString = "FILEDSN=" & "\\" & Range("appserver") &
"\" & Range("appprogdir") & "\UTILS\" & Range("dsnfile") & ";" &
"UID=" & Range("DBUSER") & ";" & "PWD=" & Range("dsn1") & ";" &
"DATABASE=" & Range("SUNDB") & ""
.CursorLocation = adUseServer
End With
AdoConn.Open
This all works fine. The database password is currently in cell
A65536 (rangenamed dsn1) - it gets there by use of a data input box
used by an administrator when setting up the model. Cell A65536 is
hidden - so your average user isn't going to find it. But if you do
find it, say by using the <f5 key to goto (range dsn1), then the
value of the password appears in the formula bar.
I could have hardcoded the password into the connection string - the
code is locked from viewing. But if the DBA should change the
password none of my models using this connection method will work
without maintaining the code. Doing it the way I've done it means all
you do is re-run the setup and provide the new password, thus
generating the right connection string at runtime.
I could use a hidden sheet to locate this password cell to make it
more difficult to find - but I can't find a way of stopping users
unhiding that sheet. Ideally I'd like to store the password somewhere
other than a cell.
Any ideas? Thanks