Q:Excel/ODBC/Login ....

[Verner Jensen, Ålborg]

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

[MSweetG222]

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
>
>
>

[Gareth]

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
>>
>>
>>