Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: Changing linked table paths from VB

    I have a linked table in access (an excel file) and I want to change the file path from a visual basic module. I have tried changing the Connection property for the corresponding tabledef but the change doesnt stick. Any Ideas?

    I also have this other problem with access changing the page formatting on my reports to portrait and 1 inch margins all around. It does this at random with complete disregard for how many times i change them back =) any help on this would be good too.

    Thanks,

    -Farnk

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Try this
    I haven't tested it


    Public Function Relink(FileName As String) As Boolean
    ' THis is one of my favourite modules to relink a front end database to an access backend database:
    ' Refresh links to the supplied database. Return True if successful.

    Dim intCount As Integer
    Dim tdf As TableDef
    Dim counttables As Integer
    Dim main As String, Command As String, GL As String
    Dim LinkFileName As String, calcPct As Integer
    Dim Check
    Dim tdfName As String
    Dim dbs As Database, qdf As QueryDef
    Set dbs = CurrentDb()

    On Error GoTo errortrap
    DoCmd.OpenForm "Message"
    Forms![Message]![Message].Caption = "Opening General Ledger File: " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    counttables = dbs.TableDefs.Count

    main = Left(FileName, InStr(FileName, "Machines") - 1) & "Main.mdb"
    GL = Left(FileName, InStr(FileName, "Machines") - 1) & "GLChart.mdb"
    For intCount = 0 To counttables - 1
    calcPct = Int(intCount / counttables * 100)
    'If calcPct Mod 10 = 0 Then
    Forms![Message]![Message].Caption = "Opening General Ledger File (" & calcPct & "%): " & Chr(13) & Chr(10) & Chr(13) & Chr(10) & FileName
    Forms![Message].Repaint
    'End If
    Set tdf = dbs.TableDefs(intCount)
    ' If the table has a connect string, it's a linked table.
    Check = tdf.Connect
    If Len(Check) > 0 Then
    If InStr(Check, "Main") > 0 Then
    LinkFileName = main
    ElseIf InStr(Check, "GLChart") > 0 Then
    LinkFileName = GL
    Else
    LinkFileName = FileName
    End If
    LinkFileName = Left(Check, InStr(Check, "DATABASE") + 8) & LinkFileName

    If tdf.Connect <> LinkFileName Then
    tdf.Connect = LinkFileName
    tdfName = tdf.Name
    Err = 0
    tdf.RefreshLink ' Relink the table.
    If Err <> 0 Then
    Relink = False
    Exit Function
    End If
    End If
    End If
    Next intCount

    On Error GoTo 0
    DoCmd.Close acForm, "Message"
    Command = "UPDATE UserSettings SET UserSettings.[Value] = '" & FileName & "' " & _
    "WHERE (((UserSettings.Code)='CurrentDatabaseFile'));"
    Set qdf = dbs.CreateQueryDef("", Command): qdf.Execute
    Set dbs = Nothing
    Relink = True ' Relinking complete.
    Exit Function
    errortrap:

    DoCmd.Close acForm, "Message"
    MsgBox "Unable to link to " & tdfName & " in the " & LinkFileName & " database!", vbCritical, "Critical Error"
    Relink = False
    End Function

Posting Permissions

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