Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52

    Unanswered: change the path to a linked table

    Hi all, happy new year!

    I am having some serious issues because I need to put a table in a seperate database.

    I have linked the table to my front end but I want to allow the user to change the location of the linked table using the variable strPathToTable = "C:\LinkedTable.mdb"

    How do I just change the path to the linked table and refresh it?

  2. #2
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52

    - with code attached

    This is the code I currently have:

    Dim db As Database
    Dim tdf As TableDef

    For intCount = 0 To intTableCount
    Set db = CurrentDb()
    Set tdf = db.TableDefs(strTables(intTableCount))

    tdf.Connect = strPathToSwitchedDatabase
    tdf.RefreshLink

    Set tdf = Nothing
    Set tdf = Nothing
    Next

    I get an error message on the line 'tdf.refreshlink', the error message states:

    Error code: 3170
    Description: Could not find installable ISAM.

    Then I click help and this is what it states:

    Could not find installable ISAM. (Error 3170)
    The DLL for an installable ISAM file could not be found. This file is required for linking external tables (other than ODBC or Microsoft Jet database tables). The locations for all ISAM drivers are maintained in the Microsoft® Windows® Registry. These entries are created automatically when you install your application. If you change the location of these drivers, you need to correct your application Setup program to reflect this change and make the correct entries in the Registry.

    MAYBE its because I am just doing it all wrong. I want to change the location of the linked table, any ideas please hep me people

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hi milks1977,

    In my Tables that I have in the BE and Linked to the Database FE, what I did was set the Linked Table Manager to Always promt for new location...that way you are given that option, so long as you tell the users where they now are. Though once I placed my Tables in the backend I never moved them so not sure this will tell the user of the location and let them select it or if it simply just Links them automatically. Just thought I'd toss that in for a bit to see if by chance it might help.

    have a Happy and Wonderful New Year!!!!
    BUD

  4. #4
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52
    hi bud, yeah I want to do it within VBA as I will be making it as a vba. Bacially I can get the path to the database file as the user edits this in a form but I want to say that the path to the linked table is incorrect and this is the new path to relink the path to the linked table and the database is located at strPathToData, any quick way of doing it within VBA?

  5. #5
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52
    Ive solved the problem, all you have to do is skip it where the tdfTabledef.name = skip table name, simple, anybody need the code PM me

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    This function works fine for me. Replace <old> with the current link and <new> with the new link.

    Sub ChangeLink(old As String, new As String)
    Dim db As Database
    Dim tdf As TableDef
    Dim i As Integer, p As Integer, temp As String

    Set db = CurrentDb()
    For i = 0 To db.TableDefs.Count - 1
    Set tdf = db.TableDefs(i)

    temp$ = tdf.Connect
    If temp$ <> "" Then
    p = InStr(LCase(temp$), LCase(old))
    If p > 0 Then
    temp$ = Left$(temp$, p - 1) & new & Mid$(temp$, p + Len(old))
    tdf.Connect = temp$
    On Error Resume Next
    tdf.RefreshLink
    '' 3625: no link
    If Err.Number <> 3625 Then
    MsgBox Format(Err.Number) & ": " & Err.Description, vbExclamation, "Problem"
    End If
    On Error GoTo 0
    End If
    End If
    Set tdf = Nothing
    Next

    End Sub

  7. #7
    Join Date
    Apr 2013
    Posts
    1
    I'd like the code. I'll PM you, if i'm not too new.

Posting Permissions

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