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

 

[Pat Hartman]

Here's an example:

Public Function ConnectToOracle()
Dim WgtDB As DAO.Database
Dim strConnect As String
Dim F As Form
    
Set WgtDB = CurrentDb()
Set F = Forms!frmLogon

gUserID = Nz(F!txtUSER_ID, "Empty")
gPassword = Nz(F!txtPSWD, "Empty")
gNetWareID = Nz(F!txtNetWareID, "Empty")

strConnect = "ODBC;UID=" & gUserID & ";PWD=security;DSN=weight;Database=WGT"
Set WgtDB = OpenDatabase("", False, False, strConnect)

strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database=farms"
Set WgtDB = OpenDatabase("", False, False, strConnect)

WgtDB.Close

End Function

The code sets three global variables to the contents of the fields on the login form. It logs into the first db using the user's own logonID and a fixed password. The idea here is that the DBA wanted everyone to log in with their own unique userID so that it would be easy to track activity by user. But we didn't want the user to know the password for their logonID because some of them were sophisticated enough to be able to circumvent the application by creating their own Access db and linking to the server tables. So although they had to know their ID, they did NOT know their password and so were not able to open the database outside of the Access app.

The second db is opened by a fixed userID and password because NO updates are done by the app to this db so it was not necessary to have individual logons for each user.

 

[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