Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Q:Excel/ODBC/Login ....
I have the same issue. Did you find a solution?
Thx MSweetG222 "Verner Jensen, Ã…lborg" wrote: Hi' there ! My situation is this; My customer have a hole bunch of different Excel workbooks - with VB code inside. In this VB code the user/pwd to the ODBC connection is hardcoded. This hardcoding is not approved by audit. Is there any smart way (opposed the insert a login-dialogbox in each of those workbooks) - to solve this problem? What I thought of, is to make a button on my menubar to etablish the ODBC connection and regardless of wich workbook I open - hold this connection open. But somethins tells me, that the code should beplaced in a workbook - and not directly in Excel.... PS. Is it possible to grap to windows login and pwd - and use with the ODBC connection? Thanx in advance... Rgds, Henrik |
#2
|
|||
|
|||
Yes this is possible.
Create an AddIn with your connection code inside you can reference all your other workbooks to this. Thus, the AddIn will open automatically whenever the user opens any of the other workbooks. Declare a connection publicly in your AddIn - see below. Create generic code within the AddIn to make the connection for you. Public cnt As ADODB.Connection Public Function fcnCreateConnection() As Boolean 'check connection already exists '- return true if so 'else try and connect ' - return true if successful End Function 'I often use a common function to query a database too Public Function fcnQueryMyDB(mySQL As String) As ADODB.Recordset 'insert code to query and return a record set here End Function With respect to the user credentials there's lots of options. You could provide a Username and Password prompt, populating the username automatically from the network login and having the user enter the password once (keep the password privately declared in a module in case you need to reconnect). Alternatively (and depending one whether it's your DB) you may be happy assuming a user connected to the network is already secure and give them access on their username alone - waiving the password entry, but you need to be careful how you do this since someone could access the DB by other means. If you're regularly querying the database (every couple of minutes, say) IMHO it's far better to keep the connection open, rather than reconnecting and disconnecting everytime since the DB (Oracle does this at least as I understand) will normally allocate resources to every time you make a connection. Every time you query just check whether it's still open and reopen if it isn't. When the AddIn closes (i.e. quitting Excel) then you should make sure you close the connection. Note, if you need to reply to me, please just reply to "microsoft.public.excel.programming". HTH, Gareth MSweetG222 wrote: I have the same issue. Did you find a solution? Thx MSweetG222 "Verner Jensen, Ã…lborg" wrote: Hi' there ! My situation is this; My customer have a hole bunch of different Excel workbooks - with VB code inside. In this VB code the user/pwd to the ODBC connection is hardcoded. This hardcoding is not approved by audit. Is there any smart way (opposed the insert a login-dialogbox in each of those workbooks) - to solve this problem? What I thought of, is to make a button on my menubar to etablish the ODBC connection and regardless of wich workbook I open - hold this connection open. But somethins tells me, that the code should beplaced in a workbook - and not directly in Excel.... PS. Is it possible to grap to windows login and pwd - and use with the ODBC connection? Thanx in advance... Rgds, Henrik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|