Linking to ODBC tables on startup using dynamic username/password

[Kurt Monroe]

VBA experts: I'm using Access 2002.

I need to link to Oracle tables, and I need to do this so that the username
and password are not stored on the Client, they are only gotten when the user
"logs on" ( having a username/password stored on the client is not allowed)

I am using Access as a front-end to the Oracle database. I recently figured
out how to use the forced logon username/password to build the connect string
to run Procedures, and that works well.

So I'm thinking that I need to force a relink to the Oracle tables everytime
the user starts up the Access app, at which time they enter the
username/password (and datasource name too if I need it)

I know how to force them to enter username and password, and I can build the
required connect string in VBA, but I don't know how to then do a
behind-the-scenes relink using the just-entered username/password. (I could
force them to enter the datasource name also and maybe even the unique part
of their Oracle "schema", that corresponds to their business "group", if I
need it to build the relink code). But I'd like to do the rest
progammatically.

So now I want to do the table relinking "dynamically" and as automatically
as possible each time the application is started; without forcing the user to
go through the relink process - they wouldn't know the tablenames.

Also, the Oracle tables come over into Access with the "schema" (owner) name
as part of their name, and they need to be renamed in order for my Access
front-end to work. The linked tablenames cannot include the schema part,
e.g., oracleUser1.TableX gets linked to and changed to be named TableX.

I've been doing this linking and renaming manually, but the users cannot, it
must be as automatic as possible without storing the username/password used
in the linking.

Any ideas?

Thank you,

 

[Jamie Richards]

Hi Kurt,

This may help with some of the things you have raised. If you want to
re-link your tables programmatically, try visiting Dev Ashish's website at
http://www.mvps.org/access/tables/tbl0010.htm.

I have written a query tool in Access that connects to a variety of
different DBMS' (Oracle being one). I use the network login ID of the user
as a default in a login form that I designed. The user is required to enter
some specific information in order to build the connection string and then
that data can be saved to the Windows Registry at the users option
(preventing them having to fill out the details over and over). Maybe a
similar approach would work for you? As for the group/schema membership,
would you be able to have a local table (or two) that stored the userID and
group/schema memberships only (not the passwords). You could then
incorporate that data into the connection routine for each user.

Just a few thoughts, I hope it doesn't make it worse for you ;o)

Jamie