Results 1 to 8 of 8
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591

    Linking to an Oracle Back End

    I've spent the last few years working with a SQL server back end and become fairly comfortable with it. Now I have a new job, a new database and a new Oracle back end. My sole mission in life is to learn everything there is to know about this db. In my previous incarnation, we simply used either a user DSN or a file DSN to link the tables. Permissions we set by the Active Directory memberships and it wasn't necessary to login to SQL Server.

    This Oracle back end db requires a login ID and password, even though a connection has already been established via a connection string and code as follows

    Code:
    Public conTPD As New ADODB.Connection
    Public conStr As String
    conStr Driver={Microsoft ODBC for Oracle};Server=TPD.WORLD;UID=guest;PWD=guest
    conTPD.Open constr
    
    'The connection is successfully established
    'conTPD.Stat = 1
    'conTPD = Provider=MSDASQL.1;
    Also, all tables are using a DSN with the same information.

    This works perfectly EXEPT you still prompted to log into the Oracle back end.




    Click image for larger version. 

Name:	Microsoft ODBC for Oracle Connect.JPG 
Views:	24 
Size:	17.4 KB 
ID:	33732


    This Oracle database, as far as I can tell , does not look at active directory groups to grant access.

    In addition, since this is such a widely distributed back end I would like to use a file DSN than can be distributed. While they seem to work in the ODBC manager, they fail when I try to link tables using them.
    Here's the DSN and the error message.

    Code:
    [ODBC]
    DRIVER=Microsoft ODBC for Oracle
    SERVER=TPD.WORLD
    Click image for larger version. 

Name:	DSN Fail.JPG 
Views:	24 
Size:	18.5 KB 
ID:	33731

    We currently have over 300 users and using User DSNs is impractical. For each new user, someone has to remote in to their PC and create a User DSN. If we change servers they would have to do that 300 times.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Paul,
    A couple of things to try:
    -use Oracle's driver instead of the Microsoft one
    -when linking the tables make sure you check the Save Password (in the Access linking wizard) for every single table; once the tables are successfully linked your users will not need to have the user or file DSN
    -Oracle connection strings I am used with are much more complex than what you show; here is an example of one:
    DSN=My Database;UID=GUEST;PWD=GUEST;DBQ=ITAPPSDB ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM =F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;O DA=F;

    Cheers,
    Vlad

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    DSN=My Database;UID=GUEST;PWD=GUEST;DBQ=ITAPPSDB ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T ;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM =F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;O DA=F;
    Ye gods .... does that all make perfect sense if you are an oracle user? After DBA=, I'm totally baffled
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    GICU, I did exactly what you suggested, the DSN worked and I saved the password, but I got this concerning message.

    Click image for larger version. 

Name:	password.JPG 
Views:	22 
Size:	24.9 KB 
ID:	33739

    This is what the DSN looks like. When I tried the same thing with the 64 bit ODBC Manager, the connection failed.

    Code:
    [ODBC]
    DRIVER=Oracle in OraClient11g_home1_32bit
    UID=GUEST
    TSZ=8192
    ODA=F
    MLD=0
    TLO=O
    FBS=60000
    FWC=F
    CSR=F
    MDI=Me
    MTS=T
    DPM=F
    NUM=NLS
    BAM=IfAllSuccessful
    BNF=F
    BTD=F
    RST=T
    LOB=T
    FDL=10
    FRC=10
    QTO=T
    FEN=T
    XSM=Default
    EXC=F
    APA=T
    DBA=W
    DBQ=TPDPROD.WORLD
    SERVER=TPDPROD.WORLD

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Paul,

    Any linked Access table has the login credentials stored not encrypted, but unless your users have access to the code or the system tables that shouldn't be a concern.

    You mention ODBC 64 bit - what version is your Office? If 32 bit use the 32 bit driver regardless if you are on a 64 bit operating system.

    Cheers,
    Vlad

  6. #6
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Vlad,

    Quote Originally Posted by Gicu View Post
    unless your users have access to the code or the system tables that shouldn't be a concern.
    I was concerned I'd made a serious breach. So this is stored in the System tables. No, they won't have access to either.

    Just gave this some more thought, not everyone has a generic logon and this would not work for them since this database, at least for the moment, does not use Integrated Security. I'm going to see about changing that. I'm not too concerned though. It's only about ten seconds out of their life to log in.



    Your suggestion about using the Oracle driver for the File DSN was very helpful. You get a gold star.

  7. #7
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Location
    Richmond, VA
    Posts
    591
    Just wondering Vlad, where are those passwords stored in Access? If I knew that, I might be able to write some code use the local UserPreferences table to link the tables with their stored password.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Paul,

    The passwords are stored in the Connect field of the MsysObjects system table and they can be viewed directly in the table or in VBA if you look at the connect property of the tabledef object.

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. SQL Server vs. Oracle as a Back End to Access
    By Aaron5714 in forum Access
    Replies: 1
    Last Post: 03-27-2015, 12:13 PM
  2. Replies: 1
    Last Post: 03-02-2015, 09:44 AM
  3. linking the back end to the front end
    By mike02 in forum Access
    Replies: 4
    Last Post: 04-24-2013, 11:15 AM
  4. Replies: 9
    Last Post: 01-17-2013, 05:14 PM
  5. Button linking back to form?
    By Raygar in forum Reports
    Replies: 1
    Last Post: 05-14-2012, 06:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums