Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC connection via Access (2003)

    I have tables in Oracle that my employer wants to use MS/Access as its front-end. They want me capture the user id when the person signs into the ODBC sign-in and then examine the Oracle Security table to see what type of access the person has and change the MS/Access Switchboard to have the options for that user.

    Can anyone tell me how I can capture the id that the user enters in the ODBC sign-in?
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC connection via Access (2003)

    By the ODBC sign-in, are you talking about the login for the Oracle back end? Is this something the user has to do specifically each time he opens the Access front end or does the connection "stick"? If the latter, you could have a user borrow a machine with different permissions, could you not?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC connection via Access (2003)

    Hi Charlotte,

    Yes, this is the back-end sign into Oracle. I have five Oracle tables that Access is linked to. When the users open the first form which accesses on of the tables the back-end sign-in appears. What I would like to do is have my own front end sign-in to capture their id and password and pass it to Oracle so they won't have to sign in twice.

    I've tried coding

    strDsn = "Driver = {Microsoft ODBC for Oracle};Server=dobtest;Uid=" & UserEnteredId & ";Pwd=" & UserEnteredPW

    but I'm not sure about how to really use it.

    thank you
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ODBC connection via Access (2003)

    So you have a form to capture the information, but you don't know how to pass the login to Oracle?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC connection via Access (2003)

    Yes, that is exacly what I'd like to do if possible.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: ODBC connection via Access (2003)

    I'm not familiar enough with the Oracle ODBC driver to know how to do what you are suggesting. However you might consider an alternative. If you give each user a shortcut on the desktop that includes their password in the shortcut, they won't get the password prompt, but you can still modify the switchboard menu based on their Access userID. They will get the password prompt for Oracle and thus only see one prompt.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC connection via Access (2003)

    Thank you Wendell. That route is a good avenue to explore. We will be deploying the database through Novell Application Launcher (NAL) and since the person is identified this might just work. Thank you.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •