Solved

Password Change in ODBC

Posted on 2000-03-21
18
571 Views
Last Modified: 2012-08-13
Using an Access database on LAN, there is a connection to Mainframe, specifically db2 table(s) using ODBC connection.  There will be multiple users accessing the "Access" database.  Since the Mainframe resets passwords every 90 days and the password and login ID are hardcoded, is there a way to set-up a parameter so that the UID(user id) and password(pwd) can be used for each user, instead of a "hardcoded" UID and pwd. Below is the code used for the hardcoded sign in to the db2 tables.
ODBC;DSN=DB2P;UID=A123B01;PWD=cool;DBALIAS=DB2P;. Any ideas??
0
Comment
Question by:rashida
  • 11
  • 7
18 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2640894
Hello rashida,

Perhaps you could create a table to Access database which is used for string password(s) and username information.

table: tblUSER
AccessUserName (text) Primary key
ODBC_UID (text)
ODBC_PWD (text) (format:password)

Then you or the users could update the record(s) in the user table when the password in server changes.

Then you need a little code that refreshes ODBC connections and saves new passwords to connection string.

Is this what you're looking for? If you like, I can post you some sample code how to do that.

regards,
paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2640900
first line: string -> store
0
 

Author Comment

by:rashida
ID: 2644404
PAASKY,
I love to see your code, but there is a problem like I mentioned above in my question about every user will and have to change there password after 90 days on mainframe. So please keep in mind that option.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2644421
Is there same userid and password for each user or do all users have own accounts in mainframe db?
0
 

Author Comment

by:rashida
ID: 2644485
all different login ID and passwords.  There Login ID remains the same but they have to change their password. Mainframe requirement.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2644507
one more question: are users using same access application from LAN or do they have local copies installed in their workstations?
0
 

Author Comment

by:rashida
ID: 2644523
IT IS ON LAN
0
 
LVL 10

Expert Comment

by:paasky
ID: 2644647
What do you think the following solution?

- when the user needs data (select/update/etc) from ODBC table, the code would link the table into Access db using his/her username and the linked table name in Access would be for eg. TABLE1_<UID>. This could be done in form open event or before starting the report etc.

- when the connection is no longer needed the link would be dropped.
0
 

Author Comment

by:rashida
ID: 2644904
It could work for me, the table they will be using it is only viewing the data and create reports from it.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Accepted Solution

by:
paasky earned 100 total points
ID: 2645062
Okay, here's some code sample I made for you. I tested it with remote Oracle database.

Add these two functions into your Module

------------------------------------------------------------------------------------------

Public Function LinkTable(strRemoteTableName As String) As String
Dim rst As Recordset
Dim strSQL As String
Dim strConnectString As String
Dim tdf As TableDef
Dim strLocalTableName As String

    On Error GoTo LinkTable_Err

    strSQL = "SELECT * FROM tblUSER WHERE AccessUserName=" & Chr(34) & CurrentUser() & Chr(34)
    Set rst = CurrentDb.OpenRecordset(strSQL)
   
    ' user information found?
    If rst.BOF And rst.EOF Then
        MsgBox "Error: User information was not found. Cannot connect to remote database"
        Set rst = Nothing
        Exit Function
    Else
        strLocalTableName = strRemoteTableName & "_" & rst![ODBC_UID]
       
        ' define your connect string
        strConnectString = "ODBC;DSN=DB2P;UID=" & rst![ODBC_UID] & ";PWD=" & rst![ODBC_PWD] & ";DBALIAS=DB2P"
       
        With CurrentDb
            Set tdf = .CreateTableDef(strLocalTableName)
            ' create new Access tabledef and link it to remote table
            tdf.Connect = strConnectString
            tdf.SourceTableName = strRemoteTableName
            .TableDefs.Append tdf
            .TableDefs.Refresh
        End With
    End If
   
    ' refresh database window (this is not required, but I added it for testing purposes)
    Application.RefreshDatabaseWindow
    LinkTable = strLocalTableName
               
LinkTable_Exit:
    Set rst = Nothing
    Exit Function
               
LinkTable_Err:
    MsgBox "Error: " & Err.Description
    Resume LinkTable_Exit

End Function

Public Function DropLinkedTable(strTableName) As Boolean

    On Error GoTo DropLinkedTable_Err
   
    CurrentDb.TableDefs.Delete strTableName
    CurrentDb.TableDefs.Refresh
   
    ' refresh database window (this is not required, but I added it for testing purposes)
    Application.RefreshDatabaseWindow
    DropLinkedTable = True
   
DropLinkedTable_Exit:
    Exit Function
               
DropLinkedTable_Err:
    MsgBox "Error: " & Err.Description
    Resume DropLinkedTable_Exit
   
End Function

------------------------------------------------------------------------------------------

Function usage example with form:

Option Compare Database
Option Explicit
Dim LocalTable As String

Private Sub Form_Open(Cancel As Integer)
    LocalTable = LinkTable("TABLE1")
    If LocalTable <> "" Then
        Me.RecordSource = LocalTable
    Else
        ' connect operation failed
        Cancel = True
    End If
End Sub

Private Sub Form_Close()
    If LocalTable <> "" Then
        DropLinkedTable LocalTable
    End If
End Sub

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2645067
Note: see the tblUSER definition in my first comment - I used that in my example function.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2645085
the LinkTable function returns the link name, TABLE1_<UID> you should use as record source in your forms, reports, code.

When dropping the linked table, pass the parameter TABLE_<UID> to DropLinkedTable function.

Please let me know if this works for you.
0
 

Author Comment

by:rashida
ID: 2645997
I HAVE ANOTHER QUICK QUESTION. WHEN THE USER CHANGE THEIR PASSWORD AFTER 90 DAYS ON MAINFRAME, HOW THAT PASSWORD WILL UPDATE TO THE LINK TABLE? DO I HAVE TO CHANGE IN THE TABLE?
WHAT IS CHR(34)?
0
 
LVL 10

Expert Comment

by:paasky
ID: 2646050
maintain user UID and PWD information with tblUSER table. When the password changes, it must be updated to tblUSER.ODBC_PWD field - nothing else is needed because above code will take the required up-to-date information from that table.

You should have a record for each user tblUSER table, for eg.

AccessUserName   ODBC_UID  ODBC_PWD
paasky           apaasky   secret
rashida          rashida   topsecret
.....

chr(34) = "  (double quote)

Paasky
0
 

Author Comment

by:rashida
ID: 2646128
Thanx for your tip. Right now i'm in the middle of something i will resume this project next week. I try it and then i will assign the points to you by next week if it is ok with you. Plesae let me  know
0
 
LVL 10

Expert Comment

by:paasky
ID: 2647998
That's ok. See you later.

paasky
0
 

Author Comment

by:rashida
ID: 2673122
As I promissed here is your earned points. ThanX againg for your help.  I need to ask you a quick question.

1. If I have 6 dropdown fields with two values "documented" and "notdocumented".I do not want any default value for those fields, and I do not want to go to the next field without selecting the value. In other words force the user to select one of the other value before go to the next filed. any suggestion will help full.
ThanX
0
 
LVL 10

Expert Comment

by:paasky
ID: 2673223
Thanks. Glad I could help.

You could place something like this into combobox's OnExit event:

Private Sub Combo15_Exit(Cancel As Integer)
    ' check that value is selected
    If IsNull(Me.Combo15) Then
        ' no, prompt user and cancel exit
        MsgBox "You must choose either value Documented or Not Documented."
        Cancel = True
    End If
End Sub

Hope this helps,
Paasky
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now