Results 1 to 10 of 10
  1. #1
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65

    Refreshing SQL links through coding

    Hi All,



    I often get ODBC connection issues on my linked SQL tables which is easily fixed by refreshing the link. The issue is database will be used by some very non-technical folks that I would like to avoid explaining this. Is there some code where on opening the database would automatically refresh these links?

    Thanks for your help,

    Eric

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Example of my code:

    Code:
    Private Sub Form_Open(Cancel As Integer)
    AttachDSNLessTable "Games", "Games", "localhost\SQLEXPRESS01", "Test"
    AttachDSNLessTable "Rates", "Rates", "localhost\SQLEXPRESS01", "Test"
    AttachDSNLessTable "Teams", "Teams", "localhost\SQLEXPRESS01", "Test"
    AttachDSNLessTable "Umpires", "Umpires", "localhost\SQLEXPRESS01", "Test"
    End Sub
    In a general module:
    Code:
    '//Name     :   AttachDSNLessTable
    '//Purpose  :   Create a linked table to SQL Server without using a DSN
    '//Parameters
    '//     stLocalTableName: Name of the table that you are creating in the current database
    '//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
    '//     stServer: Name of the SQL Server that you are linking to
    '//     stDatabase: Name of the SQL Server database that you are linking to
    '//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
    '//     stPassword: SQL Server user password
    Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
        On Error GoTo AttachDSNLessTable_Err
        Dim td As TableDef
        Dim stConnect As String
    
    If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next
    
    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=True"
    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_Err:
    
    AttachDSNLessTable = False
        MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
    
    End Function
    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
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks June! I'll give it a shot today.

    Eric

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Eric,
    Here is another version that refreshes the links instead of deleting and adding the tables:
    Code:
    Public Sub change_ODBC()
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim Newserver As String
    Dim NewDB As String
    
    
    Newserver = InputBox("Enter the network name of the server you wish to use:" & vbCrLf & vbCrLf & "Example: YOUR_NEW_SERVER", _
                               "Bind To New Server")
    
    
    NewDB = InputBox("Enter the name of the database you wish to use:" & vbCrLf & vbCrLf & "Example: YOUR_NEW_DB", _
                               "Bind To New Database")
    'If the user clicks cancel or gives a null length responce the server change will abort
    
    
    If Newserver <> "" And NewDB <> "" Then
    MsgBox "Server Selected: " & Newserver & vbCrLf & vbCrLf & "Database Selected: " & NewDB
      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
          tdf.Connect = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=" & Newserver & ";DATABASE=" & NewDB & ";Trusted_Connection=Yes"
          tdf.RefreshLink
        End If
      Next
    Else
         MsgBox "Server and database change request canceled or invalid response given."
    End If
    
    
    End Sub
    I usually save the various connection strings (like in production vs. development) in a local settings table and retrieve it from there using a dLookup, but the function above prompts the user for the values.

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

  5. #5
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad,

    Yes, this is more of what I am looking for. I'll let you know how it goes.

    Eric

  6. #6
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad, Sorry it's been awhile since I've worked on this. Is there code that can just refresh the current external SQL table links when opening the database without prompting?

    Thanks,

    Eric

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Eric,
    Something like this would do it; you need to run this first thing when you open the database in case you have a bound opening form which would interfere with the refresh (if bound to a linked table). I usually call this type of function as the very first action of an AutoExec macro then I open the main form as the following action of the same macro.
    Code:
    Public Sub fnRefresh_ODBC()
    
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    
    
    Set dbs = CurrentDb()
    
    
    For Each tdf In dbs.TableDefs
      If tdf.Connect <> "" And Left(tdf.Name, 1) <> "~" Then      'will refresh all linked tables
        tdf.RefreshLink
      End If
    Next
    Set tdf=Nothing
    Set dbs=Nothing
    
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Thanks Vlad... I will let you know. Sooner than last time I promise.

  9. #9
    DefinitelyMaybe is offline Advanced Beginner
    Windows 10 Access 2002
    Join Date
    Dec 2020
    Posts
    65
    Hi Vlad, I think it is working as in it is not causing errors or crashing! It is a bit slow when opening the database. I will test with our users who are behind some firewalls where it seems to be even slower. One thought I had was to instead of refreshing the links on start up, create a button that would refresh after the ODBC error occurred. Would that make sense?

    Thanks

    Eric

  10. #10
    keviny04 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    128
    If you get frequent ODBC errors, try updating your ODBC driver.

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

Similar Threads

  1. links subform
    By slimjen in forum Forms
    Replies: 3
    Last Post: 05-07-2013, 01:56 PM
  2. Links and Relationships Help !
    By wrkadri in forum Database Design
    Replies: 3
    Last Post: 03-08-2013, 08:09 AM
  3. Showing Database Links
    By cbh35711 in forum Access
    Replies: 3
    Last Post: 03-23-2012, 11:45 AM
  4. Testing links
    By piflechien73 in forum Forms
    Replies: 0
    Last Post: 06-01-2009, 05:09 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