Refresh Linked Tables using new credentials

[nvrwrkn]

Ok, I'm attempting to speed up and better codify my login processes. Some things that must be:

1. Have to use linked Oracle tables
2. Login credentials for app have to be used for connecting to Oracle tables

So what I was doing, was deleting the old linked tables and re-establishing the linked tables using login credentials so every time the tables are hit the current user creds are used for the connection. The problem is this takes too long to delete and re-establish in order to use users login/pass in the Oracle connection.

The goal would be merely to set up the old linked tables with the new user's logon/pass and not actually refresh them or recreate them, rather set them to the logon/pass info is available when the app hits them for data. The Oracle connection is persistent so once logged in it stays up for duration of app use.

How can I set up persistent logon info for linked oracle tables to be used throughout the app without having to refresh the links or recreate the tables with the new logon/pass info?

[rpeare]

So you're looking for a way to re-establish a link programmatically rather than dropping a table and re-adding it?

You say you are able to get the current user creds but then you delete and re-establish the tables so I'm a little confused. Typically if a person has the same user name/pw (credentials) for both windows login and their database login you can use NT authentication and never have to consider a username/pw. So are you saying that your users Network AND database logins are identical or they are different?

[nvrwrkn]

Different they are . Our entity has been around far before computers came into widespread use. We only gradually, and very lately, came around in the last few years to setting up anything resembling a enterprise level network. Our IT department has always been the rejects from elsewhere and management around here is piecemeal so our network, database setups and enterprise applications are created by different people all the time, using their own interpretation of "best practice" at the time. So it's a mess of distribution, network management and server management. We can't always use windows authentication for security purposes for database and application login because we have computers all over the organization that people walk away from and leave themselves logged in, and there are several applications which carry legal authority with their use. So a second login to the main application repository is mandatory and frequently not the same as windows authentication.

In MY app, the only way I can distribute it is without MY credentials buried within the DSN entry for the linked tables. So if I save the linked table WITHOUT saving my credentials, whenever it's accessed from the app the user is hit with the standard windows DSN driver window asking for name and password and I can't pass the user's credentials to that popup window. The only way to create the linked table using access shortcut tools is to use DSN to find and link the table. That DSN I think is saved as the connection to the linked table and asks for it when you hit it. So what I did was let a user log into MY app first, variable save their credentials and then use those variables in a VBA login string that's called during a module that creates the linked table. That way the users creds are used to establish the linked table rather than leaving the table in tact when the app closes and thinks it needs DSN to find it again.

So I drop the tables and recreate the link every time someone logs in because a linked table created the Access way leaves DSN in charge of finding the table.

So I think what I'm looking for is whether I can keep the linked tables I created with a separate connection string and somehow pass a new users creds to the linked table I created with someone elses login creds. When I left the linked tables alone and logged in with different creds and just double clicked to open them I got hit with the DSN login prompt rather than the linked tables just using my new variable creds.

Did I make the explanation worse or better?

[rpeare]

What it sounds like is that you want to alter the connection parameters without disconnecting the database and I don't know that that can be done. I've never even heard of it being attempted to tell the truth, altering connection strings on the fly yes, but that goes back to disconnecting and re-connecting your tables, what you *may* be able to do is test the connection FIRST (similar to what the test button when creating an ODBC driver does). If you can see the database then keep the existing links. If it's someone new, re-establish them. That would cut down on some of your time re-linking tables if you could accomplish it.