Oracle ODBC connection question

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

 

[rkkoller]

Well, I solved the first 2 bullet points by adding the following code to the switchboard that opens with the application. It basically runs a simple query and assumes that the password is wrong if it can't connect to Oracle. The code works, but any improvements or suggestions are always welcome. If you think of anything for my 3rd bullet point about password expiration, please let me know.


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

 

[smedvid]

How about 1 generic application login for Oracle that is static?

Here is what I've done in the past for SQL Server, but should be similar for Oracle...

Prompt user for a RDBMS login and then attempt to create a connection object to back-end DB. If that fails, then prevent user from entering system.

Dim DBConn As New ADODB.Connection
On Error GoTo error_handle
SetConnectionString
'first test the NT credentials
DBConn.ConnectionString = "driver={SQL Server};server=" & lcServerName & ";database=" & lcDBName & ";Trusted Connection=Yes"
DBConn.Open
DBConn.Close
Set DBConn = Nothing

'get user clinet id and set as the workstation id
'set the global conncet string for the application

SetConnectionString

AutoExec.RefreshLinkedTables
AutoExec.RefreshPassThroughQueries


DoCmd.OpenForm "frmMain"

normal_exit:
    
    Exit Function
    
error_handle:
    If Err.Number = -2147217843 Then
        MsgBox "You are not authorized to use the application.", vbCritical + vbOKOnly, "Title"
    Else
        MsgBox "ERROR: " & Err.Number & " : " & Err.Description, vbOKOnly + vbCritical, "Title"
    End If
    
    Application.Quit

If user successfully Then a Refresh Table Links function that loops through all linked tables and resets links.

Function RefreshLinkedTables()
Dim ThisDB As DAO.Database
Dim tdf As DAO.TableDef
Dim counter As Integer
On Error Resume Next
DoCmd.Hourglass True
Set ThisDB = CurrentDb
For Each tdf In ThisDB.TableDefs
    counter = counter + 1
    SysCmd acSysCmdSetStatus, "Loading...(" & Format((counter / ThisDB.TableDefs.Count) * 100, "Standard") & "%)  "
    If tdf.Attributes = dbAttachedODBC Then
        tdf.Connect = "ODBC;" & lcConnectStr & ";TABLE=" & tdf.SourceTableName
        tdf.RefreshLink
    End If
Next

Where lcConnectStr is your connectiuon string that also includes access permissions...

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...