Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Question Unanswered: Redirecting Links with ADO

    I use the following code to redirect a link in a database from one server to another. It works great, but I've now got a database where I need to redirect the links for more than one table. What kind of procedure can I use to minimize the code to redirect the links for 7-8 tables?

    Private Sub cmdRedirectEHILinks_Click()
    On Error GoTo RedirectLink_Err

    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.table
    Dim strBackendPath As String
    Dim strNewLocation As String
    Dim strTemp As String

    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tdf = cat.Tables("TrailerCourts")
    strBackendPath = [backendpath]
    tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tdf = cat.Tables("TrailerCourts")

    MsgBox "All EHI Links have been redirected."

    Exit_RedirectLink_Err:
    Exit Sub

    RedirectLink_Err:
    MsgBox Err.Description
    Resume Exit_RedirectLink_Err

    End Sub

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Is there any reason you don't want to use a loop?

    Loop thru X times, resetting a string variable to another table name for each value of X.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Unhappy Redirecting Links with ADO

    I have nothing against loops. In fact, I also think that's the way to go. I'm having some trouble deciding where to start the looping action. In any event so far my attempts to make my loop work have been unsatisfactory

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    My additions are in CAPS. I'm assuming all of your linking logic works properly.

    Code:
    Private Sub cmdRedirectEHILinks_Click()
    On Error GoTo RedirectLink_Err
    
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.table
    Dim strBackendPath As String
    Dim strNewLocation As String
    Dim strTemp As String
    DIM STRTABLE AS STRING
    DIM X AS INTEGER
    
    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    strBackendPath = [backendpath]
    FOR X = 1 TO 8
      SELECT CASE X
       CASE 1
          STRTABLE = "TABLENAME1"
       CASE 2
          STRTABLE = "TABLENAME2"
      ....
      END CASE
      Set tdf = cat.Tables(STRTABLE)
      tdf.Properties("Jet OLEDB:Link Datasource") = strBackendPath
      Set cat.ActiveConnection = CurrentProject.Connection
    NEXT X  
    MsgBox "All EHI Links have been redirected."
    
    Exit_RedirectLink_Err:
    Exit Sub
    
    RedirectLink_Err:
    MsgBox Err.Description
    Resume Exit_RedirectLink_Err
    
    End Sub
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •