Avoiding DB login screen everytime we run MS Access DB queries which have Oracle DB

[ashokbh98]

Hi -

Currently I have MS Access DB which has some Oracle database table reference using database link concept. I have entered DSN Name,user id and password which it asks when I try to run some queries which will fetch data from oralce DB linked tables.If some wants to use my MS Access DB, they need to have DSN and everytime they run queries related to Oracle database table reference, it will ask for DSN,user id and password. For this I need to share the user id and password credentials to everyone who wishes to use my MS Access DB which I don't want to. Is there a way to avoid the login window whenever some one wants to use my MS Access DB without asking for oracle user id and password credentials everytime? My ultimate aim would be encrypt password for others.

Thanks,
Ashok

 

[WayneRyan]

Ashok,

Wouldn't a "Trusted Connection" work for you?

Otherwise, you can dynamically link the table based on username or whatever. Just point to the server and refresh the TDF link. I don't
have the exact syntax, but the .Connect has clauses for userid,
password, trusted connection, and server type.

For Each tdf In dbs.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.Connect = ";DATABASE=" & Me.txtSomeDatabase & ";" & _
                          "Trusted_Connection=Yes"
      tdf.RefreshLink
   End If
   Next tdf

Wayne

 

[llkhoutx]

I just ripped out the hardcoded UID/PWD for Oracle tables in 500 mdbs at a Fortune 50 client. I thank Sarbanes & Oaxley for the work.

If you must use a canned UID/PWD, cycle through the MSysObjects table unpacking the Connect string getting the DSN, SERVER, (Oracle) ForeignName, and (local) Name, saving all in a permanent or temporary table, then delete the local (table) object and relink it (Oracle table) object using the Docmd.TransferDatabase with a newly constructed ODBC connection string argument which has the canned UDI/PWD.

Saving the subject info gives you an audit trail and a coding error recovery method.

 

[ashokbh98]

I did not get you completely. Could you please give me step by step to acheive this? Could you send me the code if you have?

Thanks,
Ashok

 

[llkhoutx]

I'll put you on my todo list.