Results 1 to 4 of 4
  1. #1
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287

    Relinking tables via VBA?

    Hey guys,



    I have about 60 tables that are linked via ODBC. I don't know why, but all of the tables will not connect sometimes. That isn't the problem right now, since we are only going to be using access for a few more months. Just about every time I open one of my access programs, every table will give me an ODBC call failed error. Opening up the linked table manager, selecting all, NOT prompting for a new location, and hitting ok fixes the problem.

    Is there a VBA method to relink all linked tables?

    Through google I have found 2 functions, and neither seems to work:

    Method 1:
    This one just opens the linked table manager when I run it. Nothing else happens.
    Code:
    Function relinkTables()Dim tdf As DAO.TableDef
     
        For Each tdf In CurrentDb.TableDefs
            ' check if table is a linked table
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = "odbc connection string to the DSN or database"
                tdf.RefreshLink
            End If
        Next
     
    End Function
    Method 2:
    Running ReLink("AFH_Platinum_Storis") just gives an error. Not sure if I'm doing something wrong?
    Code:
    'ReLink() Updates links of all tables that currently link to strDBName to point'to strDBName in the strLinkDest folder (if specified, otherwise the same folder
    'as the current database).
    Public Sub ReLink(ByVal strDBName As String, _
                      Optional ByVal strFolder As String = "")
        Dim intParam As Integer, intErrNo As Integer
        Dim strOldLink As String, strOldName As String
        Dim strNewLink As String, strMsg As String
        Dim varLinkAry As Variant
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
     
        Set db = CurrentDb()
        If strFolder = "" Then strFolder = CurrentProject.Path
        If Right(strFolder, 1) = "\" Then _
            strFolder = Left(strFolder, Len(strFolder) - 1)
        strNewLink = strFolder & "\" & strDBName
        For Each tdf In db.TableDefs
            With tdf
                If .Attributes And dbAttachedTable Then
                    varLinkAry = Split(.Connect, ";")
                    For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                        If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
                    Next intParam
                    strOldLink = Mid(varLinkAry(intParam), 10)
                    If strOldLink <> strNewLink Then
                        strOldName = Split(strOldLink, _
                                           "\")(UBound(Split(strOldLink, "\")))
                        If strOldName = strDBName Then
                            varLinkAry(intParam) = "DATABASE=" & strNewLink
                            .Connect = Join(varLinkAry, ";")
                            On Error Resume Next
                            Call .RefreshLink
                            intErrNo = Err.Number
                            On Error GoTo 0
                            Select Case intErrNo
                            Case 3011, 3024, 3044, 3055, 7874
                                varLinkAry(intParam) = "DATABASE=" & strOldLink
                                .Connect = Join(varLinkAry, ";")
                                strMsg = "Database file (%F) not found.%L" & _
                                         "Unable to ReLink [%T]."
                                strMsg = Replace(strMsg, "%F", strNewLink)
                                strMsg = Replace(strMsg, "%L", vbCrLf)
                                strMsg = Replace(strMsg, "%T", .Name)
                                Call MsgBox(Prompt:=strMsg, _
                                            Buttons:=vbExclamation Or vbOKOnly, _
                                            Title:="ReLink")
                                If intErrNo = 3024 _
                                Or intErrNo = 3044 _
                                Or intErrNo = 3055 Then Exit For
                            Case Else
                                strMsg = "[%T] relinked to ""%F"""
                                strMsg = Replace(strMsg, "%T", .Name)
                                strMsg = Replace(strMsg, "%F", strNewLink)
                                Debug.Print strMsg
                            End Select
                        End If
                    End If
                End If
            End With
        Next tdf
    End Sub
    Thanks in advance for any help.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use method 1. it works.
    do you check the tbl.connect on the ones that aren't relinking?

  3. #3
    snipe's Avatar
    snipe is offline Competent Performer
    Windows 7 64bit Access 2003
    Join Date
    Sep 2013
    Posts
    287
    You lost me there. More details on checking the tbl.connect? I've never really used to VBA to manipulate the application; just to manipulate my data.


    Edit: wow, I'm an idiot.

    I left this line as is: "odbc connection string to the DSN or database"

    Fixed that with the correct info, but I'm getting an error for "Could not find installable ISAM." All of these tables relink just fine via the GUI.

    So I'm stuck again.

    Edit 2:Found the problem, I had 1 table that was linked to another Db. Excluded that one. All is good now!

  4. #4
    Glenn_Suggs is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    72
    Would Method 1 work with a backend server as the ODBC connected data? We use an Access front end and an Oracle backend. I have a need to refresh the links to all backend tables since the login credentials will be changing often. I'd like to have each application access a common db in order to ensure the current user name and password are being used.

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

Similar Threads

  1. Relinking Tables
    By crowegreg in forum Access
    Replies: 2
    Last Post: 10-09-2013, 05:56 PM
  2. Replies: 4
    Last Post: 06-04-2013, 01:13 PM
  3. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  4. avoid relinking linked tables
    By Hobbes29 in forum Import/Export Data
    Replies: 1
    Last Post: 10-01-2010, 11:15 PM
  5. Relinking an ADE file to SQL tables
    By cjbuechler in forum Database Design
    Replies: 4
    Last Post: 06-05-2009, 09:48 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