Results 1 to 8 of 8
  1. #1
    Adax is offline Novice
    Windows 10 Access 2021
    Join Date
    Jun 2023
    Posts
    4

    MS Access can't remember links to linked tables MS SQL Server


    MS Access can't remember links to linked tables to MS SQL Server? Every time I have to rejoin linked Tables OBC to MS SQL server. What VBA solution would help automate this link refresh? How to do it from MS Excel?

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Do you mean you have to type in your password each time, or really relink all tables with the linked table manager?

  3. #3
    Adax is offline Novice
    Windows 10 Access 2021
    Join Date
    Jun 2023
    Posts
    4
    A bit different, because I use .dsn file. Each time after opening the database, the linked tables to the MS SQL server are unreachable and I have to reconnect them using manually the .dns configuration file.
    Then I can work with the database until it closes. The same thing happens when I open Access later

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You probably didn't check the Save Password checkbox in the linked table manager (you will get prompted for every table). I would suggest to use a DSN less connection, there are many examples on this forum of how to do that.
    Please post back with any attempted code if you get stuck.

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

  5. #5
    Adax is offline Novice
    Windows 10 Access 2021
    Join Date
    Jun 2023
    Posts
    4
    Giciu Thanks, you're right. But I didn't Save Password because I haven't trusted connection My confi DSN file is in this way:

    [ODBC]
    DRIVER=SQL Server
    UID=User1
    Pwd=Pass1
    Trusted_Connection=No
    Network=SRVDB01
    DATABASE=DB01
    WSID=SRVAA
    APP=Microsoft Office 2021
    SERVER=SRV.ZZZZZ.XX\SQLSRV01

    Now I see that it is possible to remember the password despite the lack of a trusted connection.
    How to do it best? I also use VBA MS Excel for SQL queries...

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You can try to remove all tables and add them one last time manually making sure you check the Save Password checkbox.
    Or you can add a bit of code in the startup form (or to the AutoExec macro if you use one) to relink all your tables when you open the file:
    Code:
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim Newserver As String
    DIm sConnect as String
    
    
    sConnect = "ODBC;DRIVER=SQL Server Native Client 10.1;SERVER=" & YourServer & _
                ";DATABASE=" & YourDB & ";" & _
                "UID=" & UID & ";" & _
                "PWD=" & PWD & ";"
                
    Set dbs = CurrentDb()
    '   Loop through TableDefs collection, only processing
    '   the table if it already has a Connection property.
    '   (all other tables are local ... not linked.)
    '   In other words this will only change linked tables.
      For Each tdf In dbs.TableDefs
        If tdf.Connect <> "" And Left(tdf.Name, 1) <> "~" Then 'if you have linked tables from multiple back-ends use the Instr() function to look into the Connect string for a keyword
          tdf.Connect = sConnect
          tdf.RefreshLink
        End If
      Next
    Not sure what your question is regarding Excel, whatever you do now will not be impacted by the above changes.

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

  7. #7
    Adax is offline Novice
    Windows 10 Access 2021
    Join Date
    Jun 2023
    Posts
    4
    Gicu thank you very much! Beautifull, tidying up always helps
    Can you help with ADO (or other method) in Excel? In the code below REFRESH doesn't work and I have to delete the table and add it again. How to set it for REFRESH?
    Cheers

    Code:
           If conn Is Nothing Then Set conn = New ADODB.Connection
           If R Is Nothing Then Set R = New ADODB.Recordset
                 With conn
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .ConnectionString = "data source=" & strPath
                .Open
                 End With
        
        Set kat = New ADOX.Catalog
        Set kat.ActiveConnection = conn
        Set tbl.ParentCatalog = kat
        mySrv = "SRV00"  
        DBName = "DB00"  
        myTbl = "Tbl1"
        myTbl2 = Tbl0" 
    
    
        tbl.Name = myTbl
        kat.Tables.Delete myTbl  '!!!  How to refresh without deleting? DELETE isn't good...
        tbl.Properties("Jet OLEDB:Link Datasource") = mySrv   
        tbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DRIVER=SQL Server;SERVER=" & mySrv & ";DATABASE=" & DBName & ";UID=" & myName & ";PWD=" & myPass
        tbl.Properties("Jet OLEDB:Remote Table Name") = myTbl2
        tbl.Properties("Jet OLEDB:Create Link") = True
        kat.Tables.Append tbl  
        kat.Tables.Refresh

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I don't use ADO much, but can you please expand on the "doesn't work" part. Nothing happens, you get an error (on which line)? In the code you posted you are already deleting a table and add it back so please show the original code you tried without delete. You are missing a double quote where you set the value on the Mytbl2 line, does your code compile? Also please post the entire sub including the declarations.

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

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

Similar Threads

  1. Replies: 7
    Last Post: 01-02-2023, 04:39 PM
  2. Access 2016 and SQL Server 2014 with linked tables
    By jrdnoland in forum Import/Export Data
    Replies: 12
    Last Post: 09-02-2020, 04:06 PM
  3. Replies: 4
    Last Post: 12-23-2015, 07:45 AM
  4. Replies: 0
    Last Post: 05-21-2012, 07:00 PM
  5. Replies: 3
    Last Post: 05-17-2012, 05:41 AM

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