troubleshooting Question

Linked Tables and Server Passwords

Avatar of harfang
harfangFlag for Switzerland asked on
Microsoft AccessEnterprise Software
10 Comments3 Solutions874 ViewsLast Modified:

I'm doing some "brainstorming" about linked tables and passwords. The context: I'm porting an Access database to Oracle, keeping Access as the font-end for the time being. Write access to Oracle tables it granted by user name, read access is allowed using a common "read-only" user name.

I need to connect to the Oracle database through code. I have created a set of modules that will request the user name and password the first time the connection is used. That is the easy part.

I would also like to use bound forms. This requires linked tables, which will not contain any password, or perhaps the "read-only" user name and password. That way, all forms would be available in read-only mode directly. When linked tables are stored without user name and password, Access will ask for that information, but only once. I found no way to verify the "connect state" of tables, or of the database engine. Apparently, the connection remains open during the entire session, and the password is never asked again.

This makes it difficult to use linked tables.

Some of the ideas I was playing with:

* Always create the record source of forms from code. (Problem: you can't use link fields or other automated linking tools).

* Create the linked tables after asking for the user name and password. (Problem: if the application doesn't end normally, the tables with the embedded passwords remain in the front-end, a potential risk.)

* Use "anonymous" links and open one table through code, triggering the "user name and password" prompt once. (Problem: my own code will have to do the same, so the user would have to enter the information twice, with the potential risk or having desynchronized users between tables and code).

My question:

How do you manage passwords and linked tables? The problem isn't specific to Oracle, and I'm certain many developers have asked themselves these questions before. What are the standard solutions?

Note: this is NOT a high-security environment. We only want to maintain an audit trail of modifications, so the user identification is mandatory. We do not anticipate hacking or identity theft. The goal is to make is simple to connect and start working. However, no password should be stored anywhere outside of Oracle.

IT Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 10 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 10 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros