automatically logon to oracle database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there someway we can have the access database automatically login to the
oracle
database to establish access to linked files. Right now we have a login form
that uses a pass thru query that requires the user to enter his id and
password. I would like to have the ability of doing the login 'behind the
scenes' so the user doesn't have to login. I have been able to modify the
form so that the userid/password defaults but I don't know how to eliminate
the form from being displayed? If I can hide the form how will the oracle
connection be established?
Don
 
Hi, Don.
I would like to have the ability of doing the login 'behind the
scenes' so the user doesn't have to login. I have been able to modify the
form so that the userid/password defaults but I don't know how to eliminate
the form from being displayed?
Click to expand...

The User ID and password defaults on the form? Are you storing or planning
to store this Oracle User ID and password in the Access database? If so,
for your own personal safety, please remove the User ID and password and put
this thought out of your mind. As an Oracle DBA myself, I can tell you that
I don't know of any Oracle DBA -- who has worked long and hard to secure an
Oracle database -- who wouldn't go ballistic when he found out that an
Access database developer couldn't be trusted with the knowledge of an
Oracle User ID and password.

For security reasons, the user _must_ authenticate at least once per user
session by typing a User ID and password. Your application can hold these
values in variables for usage in a later connection, but do not store the
Oracle User ID and password in the Access database.

If you choose otherwise, be prepared for many dents in your health record.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
This access process imports data from an oracle db, massages the imported
files, and then ftp's them to a remote site. We were hoping to be able to
run this process in the middle of the night without human intervention. Is
there anyway to run this process and pass it a userid/password from within a
batch file?
 
Hi, Don.
This access process imports data from an oracle db, massages the imported
files, and then ftp's them to a remote site. We were hoping to be able to
run this process in the middle of the night without human intervention.
Click to expand...

These tasks can all be done from and within Oracle, so no external process
like an Access database application is necessary. Oracle has the SQL,
PL/SQL and SQL*Plus languages available to create stored procedures,
packages, and functions that will manipulate and massage the data in any way
it needs to be manipulated. The Oracle DBA can schedule a job to run
specific stored procedures at any time, day or night, to process the data
without any human intervention whatsoever.

If no one in your organization has the skills to produce the Oracle stored
procedures, then the Oracle DBA can set up a job to export the necessary
data to text files that your Access database can then process and FTP to the
remote server, as long as that remote server doesn't need to authenticate,
because you don't want to store an FTP account's User ID and password in the
Access database, either. If authentication is required, then the Oracle DBA
can schedule another job that runs a stored procedure that later retrieves
the massaged data from the Access database and FTP's it to the remote
server.

But I would ask why there are no trained Oracle people to support the Oracle
database and the data, because Oracle is a major investment for any
organization. Not having trained people to support it can easily lead to
very expensive disasters.
Is
there anyway to run this process and pass it a userid/password from within a
batch file?
Click to expand...

Unfortunately, a batch file is just as vulnerable as an Access database,
because anyone with a text editor and access to the file can read the Oracle
User ID and password. A remote possibility of running this process
overnight -- check with your Oracle DBA first to see whether this is
acceptable -- may be to place the Access database on a Windows Server in a
locked server room, where the only personnel who have access to the room and
the computer are system and network administrators and DBA's. Microsoft
Access would need to be installed on this server and a job scheduled in the
Windows Task Scheduler to run the process at the specified time.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
I was trying not to reinvent the wheel, since we had this access database
already set up, i was looking for a way to run it without user intervention.
I guess it's not possible. (I've already written scripts that create text
files from the oracle d.b and automatically ftp's them). Since i don't play
with access too much i was looking to see if it was possible to automate the
m.s access process for the future.
Thanks for your assistance...
 
Hi, Don.

i was looking to see if it was possible to automate the
m.s access process for the future.
Click to expand...

It's possible. Access just isn't a very secure way to do it.
Thanks for your assistance...
Click to expand...

You're welcome. Good luck.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Don,

When linking the your Oracle tables you have the option to Save Password.
You may just need to recreate your table links with this option turned on.

Rob
 
Hi, Rob.
When linking the your Oracle tables you have the option to Save Password.
You may just need to recreate your table links with this option turned on.
Click to expand...

_NEVER_ save the Oracle User ID and password in an Access database, nor
advocate that others do so, lest you find dents in your health record when
your Oracle DBA finds out and goes ballistic.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
 
Gunny,

I appreciate your position regarding Oracle password security, but please
recognize that every organization has different policies and while it may be
unsecured, it also may be allowed. In fact, a DBA showed me how to do this
in the first place.

Rob
 
unfortnately the access db is not linking to the oracle tables its doing an
import of the tables via a macro.(after the import there some modifications
made to the table that we wouldn't want made to the production version). I
didn't see anything on the import that would allow me to save the
userid/password. If you know how please pass it on.
 
Don, try this VBA code:

DoCmd.TransferDatabase acImport, "ODBC Database",
"ODBC;DSN=tstDSN;UID=scott;PWD=tiger", acTable, "scott.ORACLETABLE",
"ACCESSTABLE"

If you are using a macro you can try putting this in the Database Name
argument:

DSN=tstDSN;UID=scott;PWD=tiger

Rob
 
Thanks I realized that I could add a link table and have it store the
user/pwd, then create a simple query that returns nothing and it worked. The
query stays on the screen until the processing is finished. Is there anyway
of running the query and not having it display??
 
Back
Top