Linked Tables and Server Passwords

Posted on 2009-12-16
Last Modified: 2013-11-29

I'm doing some "brainstorming" about linked tables and passwords. The context: I'm porting an Access database to Oracle, keeping Access as the font-end for the time being. Write access to Oracle tables it granted by user name, read access is allowed using a common "read-only" user name.

I need to connect to the Oracle database through code. I have created a set of modules that will request the user name and password the first time the connection is used. That is the easy part.

I would also like to use bound forms. This requires linked tables, which will not contain any password, or perhaps the "read-only" user name and password. That way, all forms would be available in read-only mode directly. When linked tables are stored without user name and password, Access will ask for that information, but only once. I found no way to verify the "connect state" of tables, or of the database engine. Apparently, the connection remains open during the entire session, and the password is never asked again.

This makes it difficult to use linked tables.

Some of the ideas I was playing with:

* Always create the record source of forms from code. (Problem: you can't use link fields or other automated linking tools).

* Create the linked tables after asking for the user name and password. (Problem: if the application doesn't end normally, the tables with the embedded passwords remain in the front-end, a potential risk.)

* Use "anonymous" links and open one table through code, triggering the "user name and password" prompt once. (Problem: my own code will have to do the same, so the user would have to enter the information twice, with the potential risk or having desynchronized users between tables and code).

My question:

How do you manage passwords and linked tables? The problem isn't specific to Oracle, and I'm certain many developers have asked themselves these questions before. What are the standard solutions?

Note: this is NOT a high-security environment. We only want to maintain an audit trail of modifications, so the user identification is mandatory. We do not anticipate hacking or identity theft. The goal is to make is simple to connect and start working. However, no password should be stored anywhere outside of Oracle.

Question by:harfang
    LVL 28

    Accepted Solution

    I'm in the middle of a project where I've developed an Access front end connecting to MS SQL db's.  I have a number of processes that switch which db is currently linked and other processes that establish temporary table links for the purposes of updating data between the SQL db's.  I'm using integrated Windows authentication so I don't have the issue with password storage.

    A couple of low-tech (inelegant?) thoughts for you though:
    create DSNs for each user on their machine (same DSN name) and create the links using the DSN
    execute a procedure at both app quit and app launch to set all linked table connection strings to use the common ReadOnly username (I know this still allows for the possibility of a user password to be present if the app exits non-gracefully and the app is launched in a manner to bypass the startup process to change existing links).

    Just throwing some ideas out there.
    OM Gang
    LVL 8

    Assisted Solution

    I would  look into DNSLESS connections for your linked tables...  Below you will find some code examples of how to do this...
    The code in the example was with SQL Server, but you should be able to do something similar to Oracle.

    Here is a link to a specific article on Oracle...
    Public Const strConnDev As String = "Your Specific COnnection String Here."
    Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stConnect As String)
        On Error GoTo AttachDSNLessTable_Err
        Dim td As TableDef
        For Each td In CurrentDb.TableDefs
            If td.Name = stLocalTableName Then
                CurrentDb.TableDefs.Delete stLocalTableName
            End If
    '    If Len(stUsername) = 0 Then
    '        '//Use trusted authentication if stUsername is not supplied.
    '        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    '    Else
    '        '//WARNING: This will save the username and the password with the linked table information.
    '        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    '    End If
        Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
        CurrentDb.TableDefs.Append td
        AttachDSNLessTable = True
        Exit Function
        AttachDSNLessTable = False
        MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
    End Function
    'Here is how to call it
    'AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")
    'get list of tables
    Public Sub getAllTables()
       Dim db As Database, tbl As TableDef, fld As Field
       Set db = CurrentDb
       For Each tbl In db.TableDefs
         If Left$(tbl.Name, 4) <> "MSys" Then
           Debug.Print tbl.Name & "      " & tbl.DateCreated & "      " & _
            tbl.LastUpdated & "     " & tbl.RecordCount
            'db.Execute "Insert into tblList (TableName) values('" & tbl.Name & "')"
           ' optional code to print all the fields
             'For Each fld In tbl.Fields
             '   Debug.Print fld.Name
             'Next fld
         End If
       Next tbl
    End Sub
    Public Sub Test1()
       ConnectOneTable "IssueAttachments", "IssueAttachments"
    End Sub
    Public Sub ConnectOneTable(sLocaltableName As String, sRemoteTable As String)
    Dim bResult As Boolean
      bResult = AttachDSNLessTable(sLocaltableName, sRemoteTable, strConnDev)
      If bResult Then
        MsgBox "Linked " & sLocaltableName & " from " & sRemoteTable
        MsgBox "Error Linking Table: " & sRemoteTable
      End If
    End Sub
    Public Sub ConnectAlltables()
    On Error GoTo Error_Handler
    Dim db As Database
    Dim rs As Recordset
    Dim rs2 As Recordset
    Dim sTableName As String
    Dim scolumn As String
    Dim b As Boolean
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from qryTableList")
    If Not (rs.EOF And rs.BOF) Then
      Do While Not rs.EOF
        If InStr(1, rs("Name"), "Old") < 1 Then
           sTableName = Replace(rs("Name"), "dbo_", "")
              b = AttachDSNLessTable(sTableName, sTableName, strConnDev)
              sSql = "Select * from " & sTableName
              Set rs2 = db.OpenRecordset(sSql, dbOpenForwardOnly)
              scolumn = rs2(0).Name
              CreateIndex sTableName, scolumn
          End If
         Debug.Print sTableName
    End If
    Exit Sub
      MsgBox (Err.Number & " " & Err.Description)
    End Sub
    Public Sub CreateIndex(sTableName As String, sField As String)
    On Error GoTo ErrorHandler
    Dim idx As String
    Dim sSql As String
    idx = sTableName & "idx"
    sSql = "Create Unique Index " & idx & " on " & sTableName & "(" & sField & ");"
    'Call CurrentDb.Execute("CREATE UNIQUE INDEX NameIDX ON myTable (field1, field2);")
    CurrentDb.Execute (sSql)
      Exit Sub
      MsgBox Err.Number & " " & Err.Description
    End Sub

    Open in new window

    LVL 58

    Author Comment

    This all seems to point to recreating the table links at startup, or rather replacing the "read-only" versions with "authenticated" versions. I can live with that.

    I did notice that Jet remembers the password of a connection for the duration of the session. I guess that "closing the session" will equate "closing the front-end". I don't believe there is a way to reset the internal connection objects (those used for linked tables).

    One question to Rog: why do you call this "DNS-less"? I might have everything mixed up, but when your connection string says "SERVER=ABC", doesn't that imply some translation of "ABC", hence some DNS involvement?

    LVL 28

    Expert Comment

    I believe Rog meant to say DSNless, i.e. not utilizing a DSN for the connection info.

    OM Gang
    LVL 58

    Author Comment

    You caught me! I wanted to write DSN (and not DNS) both times. Ah, dyslexia...

    Since I'm at it, all machines in the service have the correct DSN already created (I'm adding new tables to an existing system, which works fine already, but which isn't using Access as front-end).

    LVL 58

    Author Comment

    Re-reading. What is the "DSN execute a procedure"? Perhaps some method I missed. I know about creating linked tables providing a connection string (and creating queries with a connection string for that matter). Is this something different?

    LVL 58

    Author Comment

    Sorry, disregard the last comment. I must be getting tired. -- (°v°)
    LVL 28

    Assisted Solution

    Sorry, those two lines should be seperated.
    My first thought is you could use DSN's on each user computer.  The connection info. for the Oracle db and the user credentials will be stored in the DSN and not in the Access front end.

    My other thought is to write a procedure to set the connection string for all linked tables using the standard ReadOnly user account.  If you call this procedure at app launch and at app quit then the linked tables will not be saved with the user specific credentials.  You'd use a different procedure (or the same one with input variables) to change the linked table connection strings to use the user specific credentials as needed at run-time.  This does not prevent the possibility of the linked Oracle tables retaining the priveledged connection credentials in the event of the Access app exiting ungracefully but you mentioned you're not in a high-security environment.  Running the procedure at app launch (to relink the tables with the ReadOnly credentials) will clear out the priveleged credentials if the app was closed ungracefully previously.  Of course, there are simple methods to open the Access app without launching startup macros or processes so this method is not fullproof.

    Another thought is to capture the users credentials into a public variable.  When necessary to change the link for a table to read/write you could then run the refresh link procedure using the value in the public variable.  You'd want to promptly swith the linked table connection string back to the ReadOnly credentials as soon the read/write link was no longer necessary.  Still, if the app exited ungracefully the linked table(s) would retain their last connection stiring settings.  Also a run-time error will destroy the public variable so you'd need to make sure the error handling was robust (or make sure you had a procedure to reprompt the user for their credentials if the value of the public variable returned "").

    These are just some thoughts I've had for your Q.  In the app I'm working on now I have a public function to change linked table connection string for all tables or to link a table from a specific db (based upon input values by the calling procedure).  Since I am using integrated authentication I can store the connection strings in a lookup table without user credentials.  Wish I could be more help.

    OM Gang
    LVL 58

    Author Comment

    > Wish I could be more help.

    Don't worry, this is helping a lot. I knew I wasn't the only one trying to find elegant solutions to this problem, and I was mainly checking to see if I had overlooked something obvious, and using this thread for "brainstorming".

    You gave me an idea which I will try first thing tomorrow (well, after the stuff in my other question, http:/Q_24983664.html, which is driving me nuts!).

    If I create a new linked table with user name and password, then open it trough code to read one record, Jet will store that connection. I should then be able to create new table links *without* the password. When used during the session, the open connection is used (not asking for the password again), but if the application is closed, the user would be prompted for it...

    I could have mentioned the full background: the database is searchable at and the source data is being ported from Access to the in-house botanical database on Oracle, hosting all other projects of the Botanical Garden.

    It's all public data, entered by scientists, we only need traceability of edits and validations...

    LVL 58

    Author Closing Comment

    Sorry, I went off-line for a couple of months. Thanks for the answers; I will tackle this problem again, and perhaps open a new question then.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M… is a cloud-based customer relationship management (CRM) system. In this article, you will learn how to add and map custom lead and contact fields to your Salesforce instance.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now