ODBC Password Prompt

[SerenityNet]

I'm linking tables to an Oracle database. I've set up a system DSN that allows me to connect. However, when I run the macro that opens several make-table queries on the linked tables then I'm prompted for the Username and Password to the Oracle database.

Is there a way I can store and input these passwords, so I don't have to enter them again?

Thanks,
Andrew

 

[RichMorrison]

Andrew,

You can supply the user ID and password in a string like this:

strConnect =
"ODBC;DSN=ORAORIP;SERVER=ORIP;UID=userID;PWD=something"

This assumes you connect with code in a module.

RichM

 

[SerenityNet]

Well, I wasn't using a module, but I don't see why that would be a problem - except that I don't know how.

This is what I've done so far (that doesn't work). If you could tell me where I'm going wrong then I'd be appreciative.

I created a new module and called it "OracleConnect". The module contains the following code.

Sub OracleConnect() strConnect="ODBC;DSN=O4Me;SERVER=hunt_ora;UID=this;PWD=that"
End Sub

Then I tried to launch this from a macro, but all I've been able to do is open the module.

How do I get this to work?

Thanks in advance,
Andrew

 

[RichMorrison]

Andrew,

I don't have the time to make a complete working example for you. I suggest you look into Access "Help" and use ODBC as a keyword for search.

You should find some general examples of attaching tables with code.

RichM

 

[SerenityNet]

Thanks for the help. I wasn't able to get the code to work. I don't understand why, but I don't have time to pursue it now. I just created my linked tables and chose to save the password. Sometime, when I have the opportunity, I will give more attention to your code.

Thanks again,
Andrew