Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: Linked Table Manager 2000 to 97?

    I have a linked table manager that works fine in Access 200 but i cant get it to work in Access 97, i think .... it is someing to with DAO connection or something like that.

    can you please have a look and let me know how i can get this to work.

    cheers

    Andy
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Don't Know what Your are trying to do but all I do is

    and i go from 2000 to 97 data

    by

    First Check if can open a Table



    Code:
    If CheckLinks("setup") = False Then
    End If
    
    
    Public Function CheckLinks(TableName As String) As Boolean
    ' Check links to the Northwind database; returns True if links are OK.
    ' yes this bit is from the Northwind database
        Dim dbs As DataBase, rst As Recordset
    
        Set dbs = CurrentDb
    
        ' Open linked table to see if connection information is correct.
        On Error Resume Next
        Set rst = dbs.OpenRecordset(TableName)
    
        ' If there's no error, return True.
        If err = 0 Then
            CheckLinks = True
        Else
            CheckLinks = False
            Do_The_Link  '<== my bit
        End If
    
    End Function
    
    Sub Do_The_Link()
    Dim NewPath As String
    Dim DataBaseName1 As String
    Dim DataBaseName2 As String
    
    Dim server As String
    
    MsgBox ("HAVE TO RELINK TABLE" & vbNewLine & "PLEASE WAIT")
    server = LoadIni("EVENT", "DataPath")
    NewPath = server & "\" ' PUT NEW PATH HERE
    DataBaseName1 = LoadIni("EVENT", "HERE") '"Event Report Data.mdb"
    
    RefreshLinks DataBaseName1, NewPath, "COST"
    RefreshLinks DataBaseName1, NewPath, "setup"
    RefreshLinks DataBaseName1, NewPath, "dept"
    RefreshLinks DataBaseName1, NewPath, "Divison"
    RefreshLinks DataBaseName1, NewPath, "Email it"
    RefreshLinks DataBaseName1, NewPath, "Event"
    RefreshLinks DataBaseName1, NewPath, "Section 10 Action"
    RefreshLinks DataBaseName1, NewPath, "Section 11 Sign Off"
    RefreshLinks DataBaseName1, NewPath, "Section 3 Accident Analysis"
    RefreshLinks DataBaseName1, NewPath, "Section 4 Hazard ID"
    RefreshLinks DataBaseName1, NewPath, "Section 5 Training"
    RefreshLinks DataBaseName1, NewPath, "Section 6 Quality Event"
    RefreshLinks DataBaseName1, NewPath, "Section 7 Completed By"
    RefreshLinks DataBaseName1, NewPath, "Section 8 FileName"
    RefreshLinks DataBaseName1, NewPath, "Section 8 INVESTIGATION"
    RefreshLinks DataBaseName1, NewPath, "Section 9 Cause Basic"
    RefreshLinks DataBaseName1, NewPath, "Section 9 Cause Immediate"
    RefreshLinks DataBaseName1, NewPath, "Users"
    RefreshLinks DataBaseName1, NewPath, "log"
    RefreshLinks DataBaseName1, NewPath, "mmpyear"
    RefreshLinks DataBaseName1, NewPath, "Quality"
    
    MsgBox ("All Table ReLink")
    
    End Sub
    
    
    
    
    
    
    Function RefreshLinks(DataBaseName As String, strFileName As String, TableName As String) As Boolean
    ' Refresh links to the supplied database. Return True if successful.
    
        Dim dbs As DataBase
        Dim tdf As TableDef
    
        ' Loop through all tables in the database.
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
         If UCase(tdf.Name) = UCase(TableName) Then
            ' If the table has a connect string, it's a linked table.
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = ";DATABASE=" & strFileName & DataBaseName
                err = 0
                On Error Resume Next
                tdf.RefreshLink         ' Relink the table.
                Exit For
                If err <> 0 Then
                    RefreshLinks = False
                    Exit Function
                End If
            End If
           End If
        Next tdf
    
        RefreshLinks = True        ' Relinking complete.
    
    End Function
    then all you have to do is
    change the RefreshLinks DataBaseName, NewPath, TableName

    I have a INI file in the same folder as the Database screen
    it great for when i take the Database home to work on the first thing it does is relink all the tables ever thing Right same for when I take the database back to work
    Last edited by myle; 02-08-05 at 23:26.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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