Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    2

    Unanswered: Automate Linking Front End/Backend Db

    I have a db that I'd like to split into front end/back end so that the front end can be changed and replaced with updates. I would like to know if there is a way to set it up so that an install will replace the mdb file and then make link to the other db with the data tables.

    I supposed it's easy enough to write instruction on after replacing the old mdb with the new to link the tables, but, if there is a relatively simple way to automate it that would be better still.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I'm not sure if this is what you're looking for but here it goes anyways:

    To begin with, if all your Back End tables are linked to the Front End mdb interface and you want to update the Front End then you don't need to worry about relinking the tables if the front end is set up right. But if for some reason you want to make sure that your tables are linked then you can try using the Functions I've created below. Place them into a database module:

    Code:
    Public Function DoesTableExist(strTableName As String) As Boolean
        Dim db As Database
        Dim i As Integer
        Set db = DBEngine.Workspaces(0).Databases(0)
        DoesTableExist = False
        db.TableDefs.Refresh
        For i = 0 To db.TableDefs.Count - 1
            If strTableName = db.TableDefs(i).Name Then
                'Table Exists
                DoesTableExist = True
                Exit For
            End If
        Next i
        Set db = Nothing
    End Function
    
    
    Public Function ImportLinkedTables() As Boolean
       On Error Resume Next     'allow errors to pass by so that they can be manually processed.
        
       'Table 1 
       If DoesTableExist("My First Table Name") = False Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", "BackEnd.mdb", acTable, _
            "My First Table Name", "My First Table Name"
            'If there is a error with the 'Docmd' then there must be a error with the 'BackEnd.mdb' file. 
            'We exit this function with a false flag if this is so. You can make a msg appear if you want as well.        
            If Err Then GoTo ExitRoutine1332    
        End If
        
       'Table 2 
        If DoesTableExist("My Second Table Name") = False Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", "BackEnd.mdb", acTable, _
            "My Second Table Name", "My Second Table Name"
            If Err Then GoTo ExitRoutine1332
        End If
        
    'Do the same as above for all your table names.
        ................................................................................
        ................................................................................     
    
    ExitRoutine1332:
        If Err = 94 Then Err = 0
        If Err = 0 Then ImportLinkedTables = True Else ImportLinkedTables = False: Err = 0
    End Function
    Usage:

    If ImportLinkedTables = False then whatever, whatever, whatever
    --------------------------------------------------------------------------

    Hope this helps a little

Posting Permissions

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