Results 1 to 8 of 8
  1. #1
    hbackus is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    4

    Access Link Manager to SQL tables authentication issue

    We have a MS Access front-end that connects to a SQL back-end with ODBC System DSN that has been running OK for a few years. A new server was stood up for the SQL back-end and a new System DSN was created to connect to the SQL database because no one remembered the password for the original System DSN which is the account the Access Linked Manager uses to connect to the tables on the SQL server. Now the Access Link Manager displays a connection error. After Link Manager fails to connect to the tables and putting in the new password it connects to the tables. If the Access front-end is closed down the process is repeated. No connection to the tables, click OK on the error message, and put in the new password and the tables are connected again.
    I have not found away for the new password to stay in the Access front-end.
    Can anyone help?



    Note: I am not a programmer, I am a troubleshooter that mostly works on server OS's and switches and routers but the programmers have come to me for a solution to their dilemma. Any assistance would be greatly appreciated.

    Howard

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    There should be a checkbox when you're linking tables to allow you to save the password.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I believe the (Save Password) check box is there only when adding new tables, not when refreshing. You can go to File/Options/Current Database and under Navigation Options turn on the View System objects then look at the Connect field in the mSysObjects table in the older version using the old DSN, it might show you the password used. Alternatively you could refresh the links in VBA or you could delete all liked tables in the front-end and add them back using the new DSN and making sure you check the Save Password checkbox.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    hbackus is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    4
    Thank you for your response,

    I am trying to follow you direction on refreshing the linked tables. I do not know how to do that in VBA so I am deleting the linked tables and adding them back in. The challenge is there is 450+ tables. It maybe a while before I find out if that works. I will update when I have re-linked all tables.

    Regards,

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Here is some code I use to change linked tables from one SQL Server to another (from development to production):
    Code:
     Set dbs = CurrentDb()
    
    
      ' Loop through TableDefs collection, only processing
      ' the table if it already has a Connection property.
      ' assumes all other tables are local ... not linked to other back-ends
    
    
      For Each tdf In dbs.TableDefs
    
    
        If tdf.Connect <> "" Then
        strNewConnectionString = DLookup("[ConnectionString]", "[SettingsTable]")
        'strNewConnectionString = '"Enter your new connections string; open the msysObjects table and look at the Connect field for a table you already updated and copy that here"
            If tdf.Connect <> strNewConnectionString Then
                tdf.Connect = strNewConnectionString
                tdf.RefreshLink
            End If
        End If
      Next
    As you can see I store the connection string in a field called "ConnectionString" in a table named "SettingsTable", you can edit to match your setup or comment out that line and use the next to hard code it.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    hbackus is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    4
    Wow! that is very gracious of you. I will let you know.

    Thanks,

  7. #7
    hbackus is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    4
    Vlad Cucinschi,
    Your suggestion "...delete all linked tables in the front-end and add them back using the new DSN.." was the fix. The programmers were appreciative of the research I put in which ultimately was derived from your knowledge.
    Thanks.
    Last edited by hbackus; 07-16-2020 at 02:54 PM. Reason: remove works

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear you got it working!
    Cheers,
    Vlad

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

Similar Threads

  1. Replies: 6
    Last Post: 10-14-2017, 11:06 AM
  2. Replies: 7
    Last Post: 05-02-2017, 10:14 AM
  3. Replies: 5
    Last Post: 05-31-2016, 05:18 PM
  4. Automatic authentication for linked tables not working
    By basabnanda in forum SharePoint
    Replies: 3
    Last Post: 09-03-2015, 06:22 PM
  5. Link Manager access for split database
    By geraldk in forum Access
    Replies: 1
    Last Post: 08-21-2012, 07:51 AM

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