Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2006
    Provided Answers: 1

    Thumbs up Unanswered: Liking a table auto when Db is tfr to another machine and in a different folder

    hello all
    I have split my db into the FE and BE to hold the tables.

    I need my FE to auto link/refresh its link to the BE which it should find in the same folder as the application(FE)- and if missing should prompt to browse to it.

    The users are liable to move the folder having both FE and BE anywhere in the PC and i want to cater to this eventuality.

    this i hope will allow me to update the FE/ devp it further without having to bother about the real data.

    reading help i am feeling that to use the connect, sourcetable name, and refreshlinks methods is the way to go but--- how???

    i have gone through the forum but till now i am A LITTLE foxed!

    i am using acess 2003(XP)the file is in 2000 format though. A single user will access the db at a time on the local machine.
    Last edited by abhichoudhary; 03-29-06 at 04:16. Reason: ask the question correctly

  2. #2
    Join Date
    Feb 2006
    Floating around NW;UK

    Linking Tables

    Hi Abhi,
    I've pulled this from a working system and tried to remove irellevant code.

    HTML Code:
    Option Compare Database
    Option Explicit
    Dim strLinkCriteria As String
    Dim strDBPath
    Dim strDBFile
    Dim strBckUpDrv
    Dim dbs As DAO.Database
    Dim arrTables, eleTable
    Dim tdf As TableDef
    Private Sub Form_Open(Cancel As Integer)
    Dim fld
    arrTables = Array("Table1", "Table2", "Table3", "Table4", "Table5")
    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    strDBPath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
    Set dbs = CurrentDb
    ' Drop all the tables(links)
    For Each eleTable In arrTables
        If fExist(eleTable) Then
            Set tdf = dbs.TableDefs(eleTable)
            Debug.Print "Deleting table: " & tdf.Name
            dbs.TableDefs.Delete tdf.Name
        End If
    Next eleTable
    'Link all the tables
    For Each eleTable In arrTables
        If Not fExist(eleTable) Then
            Set tdf = dbs.CreateTableDef(eleTable)
            tdf.Connect = _
                ";DATABASE=" & strDBPath & "LiveData.mdb"
            tdf.SourceTableName = eleTable
            dbs.TableDefs.Append tdf
        End If
    Next eleTable
    Set dbs = Nothing
    End Sub
    Function fExist(strName As Variant) As Boolean
    fExist = False
    For Each tdf In dbs.TableDefs
        If tdf.SourceTableName = eleTable Then
            fExist = True
            Exit For
        End If
    Next tdf
    End Function
    Let's know how you get on.



  3. #3
    Join Date
    Mar 2006
    Provided Answers: 1

    thanks guys


    i thought there was a simpler way -im like a learner frm help files only - and the moment i have to go into too much raw code im a little afraid

    i have however found a working soln on the similar lines as you have provided
    its at a link MVPS site that IZY showed a little while ago.

    actually i copied it and removed the questioning if i wanted to relink and just ran it every time my main form loads

    also i gave it the current folder path by adding a line as under
    strNewPath = Application.CurrentProject.Path & "\myBEDataFileName.mdb"

    the original code that i downloaded is also att for anyone requiring it here-incase the link i copied doesnt work

    the doc file has two codes which need to go in seperate modules--(i dont know why they didnt work from the same module...anyone?)

    thanks guys
    Attached Files Attached Files
    Last edited by abhichoudhary; 03-29-06 at 07:18.

Posting Permissions

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