Results 1 to 4 of 4
  1. #1
    Back2Access is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    2

    Access 2010 login to connect to linked tables

    Hello experts,

    I have the following requirements and want to consult for the best possible advice for implementing it:

    1. We have an Access 2010 database (.mdb format).
    2. The database contains a mix of local tables and linked (SQL Server and Sybase Adaptive Server Enterprise (ASE)) tables. SQL Server and Sybase ASE tables are currently linked using ODBC DSNs.
    3. The local and SQL Server tables connect OK when the database is opened.
    4. However, the Sybase Adaptive Server Enterprise (ASE) tables cannot save passwords, so for those tables (80+), the user is prompted for the password for each table before letting them open the table. This is very cumbersome to users.
    5. The admins of the Sybase ASE database informed me that there is no way to save passwords for Sybase ASE ODBC data sources.

    Is there any way to set up item #4 such that the user can be prompted for a user/password combination, and then that user/password combination is securely passed to VBA code that can link the tables using connection strings? I can figure out most of the coding details (except I would appreciate help on the proper connection string method to use - which includes that we have to encrypt the password when sending it to Sybase) on my own but want to know if this general approach can work, and especially if it is viable or just too insecure to attempt.

    Thanks in advance for any help - and please let me know if you need any more information in order to assist.



    - Back2Access

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    mdb is not a 2010 database - it is 2003 or older

    Bing: access vba odbc connection password

    Does this help http://www.mrexcel.com/forum/excel-q...onnection.html

    The password input could be concatenated into the connection string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also look at

    The Connection Strings Reference
    http://www.connectionstrings.com/

    There is an example for Sybase Adaptive.

  4. #4
    Back2Access is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    2

    Access 2010 login to connect to linked tables

    Quote Originally Posted by June7 View Post
    mdb is not a 2010 database - it is 2003 or older

    Bing: access vba odbc connection password

    Does this help http://www.mrexcel.com/forum/excel-q...onnection.html

    The password input could be concatenated into the connection string.
    Thanks, that was helpful. I realize now I needed to be thinking about another question entirely!

    1. I was able to use Access VBA to create a DSN-less connection to Sybase, including the encrypted password. It looks something like this (sample data used)

    Code:
    Option Compare Database
    
    Sub Connect_ToSybaseASE()
        Dim conn As ADODB.Connection
        
        Set conn = New ADODB.Connection
        With conn
            ' DSN-less connection using the ODBC driver
            .Open "Driver={Adaptive Server Enterprise};" & _
            "Server=my-dbserver;" & _
            "Port=0000;" & _
            "DB=mydb;" & _
            "EncryptPassword=1;" & _
            "UID=myuser;" & _
            "PWD=mypwd;"
            
            If conn.State = adStateOpen Then
             MsgBox "Connection was established."
            End If
        End With
        
        Set conn = Nothing
        
    End Sub


    2. The current client's database is set up to use an ODBC DSN, so I don't think the DSN-less connection approach will work unless the application is rewritten, which is not a viable option at this point.

    So I think the better question is - What is the best way to re-link the existing Sybase linked tables now that they require an encrypted password that the ODBC DSN does not allow to be saved?

    I need something secure, so I don't know if that involves upgrading this mdb to accdb. The database as a whole is currently password-protected, but I don't know whether it is better to (a) create a login form that gets the user id and password from the client, which I would then pass to a VBA function to loop over the Sybase tables to re-link them and provide the encrypted password to the Sybase server or (b) hard-code the uid and password for this particular user (the only one using the application) since it is the same user id/password used to connect to all of the Sybase tables. Also this is a read-only connection so no updating is concerned.

    Thanks again for any help. I appreciate your patience as well - I realize this is a simple concept in principle but it seems there are several tricky variables that are tripping me up.

    - Back2Access

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

Similar Threads

  1. Replies: 6
    Last Post: 11-10-2014, 03:25 PM
  2. Replies: 4
    Last Post: 02-11-2014, 02:40 PM
  3. Replies: 3
    Last Post: 05-17-2012, 05:41 AM
  4. Linked tables SQL Server login popup
    By geremore in forum Programming
    Replies: 3
    Last Post: 08-16-2011, 12:47 PM
  5. access 2010 windows7 nas linked tables
    By mikeb in forum Access
    Replies: 7
    Last Post: 03-18-2011, 03:59 PM

Tags for this Thread

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