Authenticate Once Only For Oracle

How do I specify an Oracle connection so that the user
only has to enter there username and password once
regardless of how many times the module us called?

When I used to connect to our old Ingres DB using an ODBC
DSN with the code copied below, . And this ensured the
user only had to authenticate once and use the template as
many times as they needed.

Set ws = DBEngine.CreateWorkspace
("MyWorkspace", "admin", "", dbUseODBC)
Set cn = ws.OpenConnection("ODBCConnection",
dbDriverNoPrompt,
False, "ODBC;DATABASE=live;UID=;PWD=;DSN=remus")

I'm trying to update this template and connect to Oracle
using ADODB, but if I leave the username and password
blank in my connection string (as I have done above) it
fails ask for the logon credentials:

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=MSDAORA;" & _
"Data Source=oracle_db;"

Is this possible with ADODB?

TIA,

Colin

Colin
Tue Aug 10 11:05:59 CDT 2004


>-----Original Message-----
>Why not create a user form to gather this information and
then to store it
>in the Registry somewhere (but encrypted) and then each
time you need to
>get this information all you need to do is to read the
registry.
>
>Of course you could put the date and time in there as
well so that each
>day this information is requested again.
>
>Perhaps you could write this as an Active-X DLL and then
call it from Word
>or from any application rather than just have it as pure
VBA within Word.


Hi Malc,

I did consider writing it to the registry, but that idea
feels very very wrong.

I'm convinced it must be possible to do this without
permanently storing the password somewhere. Its probably
something really simply too because I cant find any posts
from other users in Google about it. I doubt I'm the
first person to try and do this, so I must have missed
something somewhere.

Colin

anonymous
Wed Aug 11 04:27:43 CDT 2004


>-----Original Message-----
>Why not create a user form to gather this information and
then to store it
>in the Registry somewhere (but encrypted) and then each
time you need to
>get this information all you need to do is to read the
registry.
>
>Of course you could put the date and time in there as
well so that each
>day this information is requested again.
>
>Perhaps you could write this as an Active-X DLL and then
call it from Word
>or from any application rather than just have it as pure
VBA within Word.


I'm still struggling with this. The macro runs when a
button is pressed in the template, it requests an employee
number and then connects to the database to get the data
about the employee number. Every time the button is
pressed its having to request the authentication
information.

I think I'm going to have to try your registry idea. Is
encryption possible in VBa without any third party tools?
I dont suppose you have any examples of this do you?

Cheers,

Colin