Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Tables (2003)

    I have a set of tables where the link has to be changed on a monthly basis. The path to these tables stays more or less the same, but with just the month name changing. Is there anyway I can change the link in VBA wiht the user just typing in the new month name?
    Thanks

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

    Re: Linking Tables (2003)

    You could use the following procedure:

    Sub ChangePath()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String
    Dim strOld As String
    Dim strNew As String

    On Error GoTo ErrHandler

    strOld = InputBox("Enter the old month")
    If strOld = "" Then
    MsgBox "You must supply a value!", vbExclamation
    Exit Sub
    End If
    strNew = InputBox("Enter the new month")
    If strNew = "" Then
    MsgBox "You must supply a value!", vbExclamation
    Exit Sub
    End If
    Set dbs = CurrentDb
    For Each tdf In dbs.TableDefs
    strConnect = tdf.Connect
    If Not strConnect = "" Then
    strConnect = Replace(strConnect, strOld, strNew)
    tdf.Connect = strConnect
    tdf.RefreshLink
    End If
    Next tdf
    MsgBox "Links updated.", vbInformation

    ExitHandler:
    Set tdf = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    You can call this code from the On Click event procedure of a command button on a form, for example. the user will be rpompted to enter the old name and the new name.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Suffolk, England
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Tables (2003)

    Thanks, that's just what I wanted!
    Allison

Posting Permissions

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