calling oracle stored procedure in ms access without saving password in pass through query

Question

I am working on MS ACCESS 2003, i need to call oracle stored procedure through ms access. I am using pass through query to call the procedure. I have created user DSN with Microsoft driver for ODBC and successfully able to call the procedure. ODBC;DSN=DSN_NAME;UID=USER_NAME;PWD=*******;DBQ=MY_SERVER

But my worry is to save the password in pass through query, some times password changes then i have to change the connection string for that pass through query every time.

Is there any other way to calling the procedure via pass through query without changing the connection string when password gets change or is there any code in vba to doing the same. I am looking for dynamic way where calling procedure would be easy without changing password to each and every pass through query by going to its properties. Thanks !!

score 1 · Answer 1 · answered Feb 05 '15 at 11:08

Yes this is possible however you need to know or in other words be able to retrieve the password somehow within your application. In a production line application you would use the user(login) password to create a connection string to access the back-end tables.

The structure would look similar to this:

Create a module to host connection related public functions.
Create new function GET_CONNECTION_STRING() as String: Which will return your connection-string including logged in user's password.
loop through the table definitions/ query definitions in your database and update the .connect property.
In most cases you would change the .connect property and use the .RefreshLink to refresh/connect manually.
You need to add a new reference to use DAO object. (Microsoft dao objets, or activex data obects or ado objects whichever type you want to use)

some startup code:

dim db  as dao.database
set db = currentdb
dim tdf as dao.tabledef

For Each tdf In db.TableDefs
    If tdf.connect <> vbNullString Then
        tdf.connect = GET_CONNECTION_STRING & ";TABLE=" & tdf.name
        'if you want to manually refresh uncomment below line
        'tdf.refreshlink
        End If
    End If
Next tdf

adding above function to a startup macro will ensure all the linked tables (queries you need to perform extra) are updated with the newest connectionstring.

try and post your code when you are stuck.

score 1 · Answer 2 · answered Feb 05 '15 at 23:57

Any linked table(s) and that of including “saved” pass-though quires in Access does NOT require the password to be included in that string. If you leave out the password, then a SINGLE logon to the Oracle database will THEN allow ALL linked tables and pass though quires to run and do so without a password.

The first step is to setup your tables as DSN less, and DON’T include the password (you likely best to delete existing linked tables).

To link using above but NOT include pass-word means you FIRST will have to execute a logon into the database. Once done, then you can link your tables and pass-though quires. So a one time link of tables as DSN less and you are off to the races.

Once above is done, then any and all connections (including pass-though query) will all work and work without having to include the user name.

The above thus means you don’t have to re-link for different users logging into the database.

To run a pass-through query, you can then use this one line of VBA code:

CurrentDb.Execute.QueryDefs("MyPassQuery").execute

How to link with DNS-less is outlined here: http://www.accessmvp.com/djsteele/DSNLessLinks.html

How to “cache” the user logon, and NOT require user + logon in the linked connections or ones used for pass-though is outlined here:

Power Tip: Improve the security of database connections

http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx

So you can on startup execute the logon one time, or prompt the user for a logon, and from that point on your application and pass-=though queries will run without required a password. And this ALSO means you can have different users logon and NOT have to re-linked the existing tables (and pass-though quires).

calling oracle stored procedure in ms access without saving password in pass through query

2 Answers2