Authenticate Once Only For Oracle

[Colin Steadman]

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

 

[Malcolm Smith]

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.

- Malc
www.dragondrop.com

 

[Colin Steadman]

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

 

[Malcolm Smith]

Another option then is to have an Active-X .exe running on the machine
which holds the data in a class. This then does the business in the
same way as the registry and as long as you keep the Active-X .exe alive
then this can store the information for you.

Perhaps this would be the way forwards then?

- Malc

 

[Guest]

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

 

[Malcolm Smith]

Colin

I usually made my very (simple) encryption tool by munging up the ASCII
codes, adding in random values and then reversing it. I know, it's not
mega-secure but it stops prying eyes.

I do think that MS has an encryption class somewhere. But I have never
used it.

Of course, the Active-X .exe would be the best way; it will just sit there
in memory and doesn't put the data onto disk.

- Malc

 

[Colin Steadman]

Of course, the Active-X .exe would be the best way; it will just sit there

in memory and doesn't put the data onto disk.

I'm not sure what you mean by Active-X .exe, could you point me at an example...

Thanks again,

Colin

 

[Malcolm Smith]

Colin

The best thing to do is to hunt through the MSDN for examples on Active-X
..exes. You are aware of what an Active-X DLL is; as a reminder (sorry of
this is grandmother egg sucking stuff) it's an external library of
functions which can be called from your application.

Active-X DLLs are usually built in VB, C++ and so on and offers a COM
interface which you work with as you would with ADO, for example.

Now, a DLL runs in the same process space as your application but an .exe
runs in its own space so one can have more than one application talking to
the same instance of the executable and getting the data from it. In
other words it's sharing information between applications.

I've done this before once when I needed to hold information from one
application and present it to another. I basically made a special
clipboard type thing. I will see if I can locate this code (it was
written a hundred years ago so I have no idea where it is) but you could
search for Shared Memory or something like that on MSDN.

If I can locate the example of how to do this then I will post some code
onto my site.

- Malc