Oracle ODBC Connection Help Needed

[rkkoller]

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!!

 

[Banana]

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.

 

[namliam]

- 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.

- 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.

- 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!

 

[rkkoller]

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.

 

[Banana]

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.

 

[rkkoller]

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

 

[namliam]

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.

 

[rkkoller]

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.

 

[namliam]

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??

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.

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