Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Back End Links (97 & 2000 & 2003)

    Is there a way to set the links to a back end file such that the links automatically default to the folder that the front end is in? I sometimes move the front end and back end data bases from one computer to another, but I always have to go into the main application, delete the existing links and then set the links to the new address. I would dearly like to avoid this if possible.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back End Links (97 & 2000 & 2003)

    You could re-link the tables in code when the database is opened. Here is a function that will re-link tables. It uses DAO, so you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    (Note: for Access 97, the DAO version is 3.5, and CurrentProject.Path isn't valid, you must extract the path from CurrentDb.Name)

    <img src=/w3timages/blueline.gif width=33% height=2>
    <code>
    Public Function Set_Links() As Boolean
    Dim tdf As DAO.TableDef
    Dim strBackend As String

    On Error Resume Next

    ' Substitute name of backend database
    strBackend = CurrentProject.Path & "Backend.mdb"

    ' Loop through tables.
    For Each tdf In CurrentDb.TableDefs
    ' If Connect property is non-empty, table is linked.
    If Len(tdf.Connect) > 0 Then
    ' Set Connect property
    tdf.Connect = ";DATABASE=" & strBackend
    Err.Clear
    tdf.RefreshLink ' Try to re-link the table.
    If Err Then
    ' Something went wrong
    Set_Links = False
    GoTo ExitHandler
    End If
    End If
    Next tdf
    Set_Links = True ' Linking succeeded.

    ExitHandler:
    Set tdf = Nothing
    Exit Function
    End Function
    </code>

    <img src=/w3timages/blueline.gif width=33% height=2>

    Call this function from an AutoExec macro, or from the OnLoad event of the startup form of the frontend database:
    <code>
    Private Sub Form_Load()
    If Set_Links = False Then
    MsgBox "Couldn't link to backend.", vbCritical
    Quit
    End If
    End Sub</code>

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Back End Links (97 & 2000 & 2003)

    Thanks Hans,

    I'll give it a try.

    So, for 97 do I change

    strBackend = CurrentProject.Path & "Backend.mdb"

    to

    strBackend = CurrentDb.Name & "Backend.mdb"

    ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back End Links (97 & 2000 & 2003)

    No, CurrentDb.Name is the full name and path of the frontend database. In Access 97, you can put the following function in a standard module:

    ' Extracts the file path from the specified full name

    Public Function GetPath(strName As String) As String
    Dim i As Integer
    If InStr(strName, "") = 0 Then Exit Function
    i = Len(strName)
    While Mid(strName, i, 1) <> ""
    i = i - 1
    Wend
    GetPath = Left(strName, i - 1)
    End Function

    You can use it as follows:

    strBackend = GetPath(CurrentDb.Name) & "Backend.mdb"

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Back End Links (97 & 2000 & 2003)

    If you do not want to use code you can use the linked table manager to fix the links without having to delete the tables and relink them.

    Go to Tools..Database Utilities... Linked table Manager ( actaully it is in a different place in 97)

    Select All Tables, click the box "prompt for a new location" then click OK. Access will prompt for the location, then update all the a tables.

    I tried automatic relinking in Code with Access 2003 (using ADOX.Catalog) and it was tediously slow if the database ran over a network.
    Regards
    John



  6. #6
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Back End Links (97 & 2000 & 2003)

    Thanks Hans, the code worked just fine although I have yet to try the 97 version.

    Thanks John, I guess I never noticed the "always prompt for new locattion" check box. That certainly simplifies everything. In my case the code worked fine and I noticed no slowness -- but then I'm not working over a network and it is a relatively small database.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back End Links (97 & 2000 & 2003)

    Because of <post#=425,254>post 425,254</post#> I thought you wanted something more automatic this time...

  8. #8
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Back End Links (97 & 2000 & 2003)

    Hi Hans,

    Well -- you caught me on that one. I'm afraid my memory isn't as good as it used to be -- although! I did recall doing something like that a year or so ago for a missionary data base for our church and I looked up the code. In that case I wasn't linking to tables in a back end (didn't have a back end), but I had links to files that contained pictures and documents, and I was having to change the filespec every time I moved the data base. Loungers helped me with that one, and it worked great.

Posting Permissions

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