Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-26-2009, 06:06 AM   #1
rkkoller
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
rkkoller is on a distinguished road
Question Oracle ODBC Connection Help Needed

I have a Access front end with an Oracle back end. I set up the connection with the typical Get External Data > Link Tables and selecting an ODBC Data Source. I have some issues with the way this works:

- I'd rather have the user log into Oracle immediatly when they open the application, but currently the Oracle log-in doesn't show until they open a form or report that actually accesses the Oracle data.

- If they enter a wrong password, no error message is shown. The form simply opens and whatever piece is populated by Oracle is blanked out. So, they say my app is broken when it was just them entering the wrong password.

- When a password expires, the window that pops up in Access for changing an Oracle password does not show any errors, such as if a user does not include a special character. So, they think they have changed their password when in fact they havn't because they didn't meet the password requirements. So, they think their account is broken because the "new" password won't work.

Anyway, can you point me in the right direction for some code that will require the user to log into Oracle when they open an application and that will also catch any password errors and inform the user of the error.

Thank you!!

rkkoller is offline   Reply With Quote
Old 03-26-2009, 06:11 AM   #2
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Oracle ODBC Connection Help Needed

Note that the behavior also may be affected by how your Oracle DBA has the Oracle configured. It's possible that the DBA may have created a special table in Oracle that instructs Jet (Access's engine) to not save any password even if you want to.

If you want to save the password (and understand this is a big security risk), you could check and make sure the "Save Password box is checked when you link the table. It will then never prompt you for the password again.

But if you don't or can't, then one way to work with this is to have a start up form opening up to prompt user to enter the password. You can then use VBA to connect to Oracle programmatically and you can handle error, including wrong password in this manners and behave accordingly.

I'm not if it'll handle the changing of password, though.

One more possible option is to look into using a different Oracle ODBC driver (e.g. there are two driver I know about, one provided by Microsoft and other provided by Oracle) and see if it's more stable in that respect.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 03-26-2009, 06:54 AM   #3
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Oracle ODBC Connection Help Needed

Quote:
Originally Posted by rkkoller View Post
- I'd rather have the user log into Oracle immediatly when they open the application, but currently the Oracle log-in doesn't show until they open a form or report that actually accesses the Oracle data.
Execute a (small) query to the Oracle system like:
Select Sysdate from dual;

or something, this will trigger the connection & password prompt.

Quote:
Originally Posted by rkkoller View Post
- If they enter a wrong password, no error message is shown. The form simply opens and whatever piece is populated by Oracle is blanked out. So, they say my app is broken when it was just them entering the wrong password.
Make sure the above query (from dual one) returns a proper value before allowing the user to continue.
Quote:
Originally Posted by rkkoller View Post
- When a password expires, the window that pops up in Access for changing an Oracle password does not show any errors, such as if a user does not include a special character. So, they think they have changed their password when in fact they havn't because they didn't meet the password requirements. So, they think their account is broken because the "new" password won't work.
This is an oracle specific setting, I have never been able to get this working either.

Sorry and Good luck!

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 03-26-2009, 07:14 AM   #4
rkkoller
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
rkkoller is on a distinguished road
Re: Oracle ODBC Connection Help Needed

Thanks for the quick replies. I tried the Microsoft ODBC for Oracle driver, but it didn't play nice with any tables that had a CLOB field.

As for running a query right on application open (I guess I will run this on the main switchboard that opens with the app), I like that idea. I guess if the query does not return a value, then they obviously didn't enter the password correctly, so I display an error message and somehow make them log-in again.

For changing expired passwords, the DBAs set up a website on our intranet for doing this. I wish I could somehow have Access catch this situation and display a link to the intranet website instead of displaying the default change password screen. Our intranet site for changing passwords gives all the appropriate errors such as not including a special character. Any advice on this would be greatly appriciated.

Thanks again for the advice ya'll have given so far.
rkkoller is offline   Reply With Quote
Old 03-26-2009, 07:19 AM   #5
Banana
split with a cherry atop.
 
Join Date: Sep 2005
Posts: 6,315
Thanks: 0
Thanked 90 Times in 72 Posts
Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all Banana is a name known to all
Re: Oracle ODBC Connection Help Needed

Well, I've not worked with Oracle before, but I suppose if I were in your shoes, I'd want to ask Oracle DBA if there is a mean of querying Oracle of how soon the password will expire and if it's less than five, incorporate this into your initial log in query and display a warning messagebox to your user "Password will expire in..." and have yoru users go to the intranet site to change the password before they get locked out...

Just an idea.
__________________
If relation-valued attributes and arbitrarily complex types are wrong, then I don't wanna to be right!
Founder of 'Blame the Developers First' crowd.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Banana is offline   Reply With Quote
Old 03-26-2009, 07:54 AM   #6
rkkoller
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
rkkoller is on a distinguished road
Re: Oracle ODBC Connection Help Needed

Thanks Banana, I will have a chat with my DBA.

By the way, below is my code that I added to the switchboard that opens with the application to force the user to log-in and catch if they enter their password incorrectly. It works, but any advice or improvements are always appriciated.

Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Form_Open


Query_Run:
QryString = Nz(DLookup("ACTIVITY_TYPE_ID", "DB_Login_Query"))


Exit_Sub:
Exit Sub

Err_Form_Open:
If Err.Number = 3151 Then
MsgBox "The Oracle username or password you entered is incorrect. Please try again."
Resume Query_Run
Else
MsgBox Err.Number & " - " & Err.DESCRIPTION
Resume Exit_Sub
End If

End Sub
rkkoller is offline   Reply With Quote
Old 03-27-2009, 03:15 AM   #7
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Oracle ODBC Connection Help Needed

QryString is not defined??
Do you have checked the "Require Variable Declaration" ?? You should declare ALL your variables always.

I would probably not use DLookup but open to a recordset or something.

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Old 03-27-2009, 05:28 AM   #8
rkkoller
Registered User
 
Join Date: Mar 2009
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
rkkoller is on a distinguished road
Re: Oracle ODBC Connection Help Needed

Quote:
Originally Posted by namliam View Post
QryString is not defined??
Do you have checked the "Require Variable Declaration" ?? You should declare ALL your variables always.

I would probably not use DLookup but open to a recordset or something.
QryString is defined, just not within the sub. It is defined at the top of the form code under Option Explicit.

If you think the recordset would be better, how would I accomplish that? My intention with the DLookup is to simply force Access to connect to the DB and open the login window.
rkkoller is offline   Reply With Quote
Old 03-27-2009, 05:40 AM   #9
namliam
The Mailman - AWF VIP
 
Join Date: Aug 2003
Location: Amsterdam/The Netherlands
Posts: 11,388
Thanks: 0
Thanked 800 Times in 787 Posts
namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light namliam is a glorious beacon of light
Re: Oracle ODBC Connection Help Needed

Quote:
Originally Posted by rkkoller View Post
QryString is defined, just not within the sub. It is defined at the top of the form code under Option Explicit.
Why use a public variable for this??

Quote:
Originally Posted by rkkoller View Post
If you think the recordset would be better, how would I accomplish that? My intention with the DLookup is to simply force Access to connect to the DB and open the login window.
Not saying its better, different, not better.
Code:
Private Sub Form_Open(Cancel As Integer)

On Error GoTo Err_Form_Open

Query_Run:
    dim rst as dao.recordset
    set rst = currentdb.openrecordset("DB_Login_Query")
    ' maybe add some checking here
    rst.close
    set rst = nothing
    
    
Exit_Sub:
    Exit Sub
    
Err_Form_Open:
    If Err.Number = 3151 Then
        MsgBox "The Oracle username or password you entered is incorrect.  Please try again."
        Resume Query_Run
    Else
        MsgBox Err.Number & " - " & Err.DESCRIPTION
        Resume Exit_Sub
    End If
    
End Sub

__________________
ruoY yppah namliaM si syawla yppah ot pleh
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.



To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Remember to use the scales (Bottom left) or the thumb (bottom right)

Always looking for new challenges (Amsterdam Area)
namliam is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ODBC connection to Oracle. christshis General 9 09-30-2008 06:26 AM
Beginner's Guide to managing ODBC Datasources. Banana General 4 11-27-2007 11:41 AM
Oracle ODBC Connection Mish_h General 1 04-19-2006 06:16 AM
How to 'grab' the ODBC connection used by linked tables DanR General 5 04-13-2004 07:52 PM




All times are GMT -8. The time now is 06:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World