Create Link table through DAO code

[icemanmt78]

I want to create a link table to another Access database within an existing database. I want to achieve this through a code only process. Any ideas??

HELP!

[JoeyCode]

Yup. I had this same issue a couple of months ago. You can use the DAO 'SourceTableName' and 'Connect' properties of the TableDef object to update or create a link. Here's a function I wrote that accepts an open DAO database reference, a database to link to (with full path) and the table name to link, and does the linking. It will change a link only if the SourceTableName is the same as the new one, and it will not attempt to mess with the connect property of an existing table that is not a linked table (the name of your link can be different than the original table, or SourceTableName, but this code assumes you want them to be the same). Here ya go, modify to meet your needs:

Public Function bDoTableLink(ByRef daodMainDatabase As Database, ByVal sDatabaseToLink As String, ByVal sTableToLink As String) As Boolean

    'This function will link a table (sTableToLink) from an Access database
    ' (sDatabaseToLink) inside the another database (daodMainDatabase reference).
    ' It checks for a current existing table of the same name...if the table
    ' already exists and it's not a linked table, this function will exit
    ' with FALSE.  If the table is linked, the link will be changed, if necessary,
    ' to link to the new location.  If the table link doesn't exist,
    ' it will be created.
    '
    'Uses Access 97 to Access 97 standard linking conventions.
    '
    'Parameters:
    '
    '   daodMainDatabase = A reference to a currently open DAO Access database.
    '   sDatabaseToLink = Filename (with full path) of Access DB to
    '       to link table FROM.
    '   sTableToLink = Name of table to link.
    '
    'Returns:
    '
    '   TRUE if linking works or is already present, or
    '   FALSE if not or an error occurs.
    '
    
    Dim daotdfLink As TableDef
    Dim sLinkHeader As String
    Dim bNoTable As Boolean
    Dim sWork As String
    
    On Error GoTo VeryBad
    
    'Set the header part for the database link
    sLinkHeader = ";DATABASE="
    
    'First, try opening an existing table
    bNoTable = False
    On Error GoTo NoTable
    Set daotdfLink = daodMainDatabase.TableDefs(sTableToLink)
    On Error GoTo VeryBad
    
    'Check if table exists
    If bNoTable = False Then
        'Table there, check its connect value
        sWork = UCase(Trim$(daotdfLink.Connect))
        If sWork <> "" Then
            'There's a connect link, check it
            If InStr(sWork, UCase(sDatabaseToLink)) > 0 Then
                'Desired link is already there, verify SourceTableName property
                If UCase(Trim$(daotdfLink.SourceTableName)) = UCase(sTableToLink) Then
                    'Good link, exit with TRUE
                    bDoTableLink = True
                    Set daotdfLink = Nothing
                    Exit Function
                Else
                    'Linked table with a different source table, exit with FALSE
                    bDoTableLink = False
                    Set daotdfLink = Nothing
                    Exit Function
                End If
            Else
                'Link is different, check SourceTableName
                If UCase(Trim$(daotdfLink.SourceTableName)) = UCase(sTableToLink) Then
                    'It's good, keep going
                Else
                    'Source table different, exit with FALSE
                    bDoTableLink = False
                    Set daotdfLink = Nothing
                    Exit Function
                End If
            End If
        Else
            'No connect value, not a linked table, exit with FALSE
            bDoTableLink = False
            Set daotdfLink = Nothing
            Exit Function
        End If
    End If
    
    'If here, table is ready to be created or edited
    If bNoTable = True Then
        'No table, add one
        Set daotdfLink = daodMainDatabase.CreateTableDef(sTableToLink)
        daotdfLink.Connect = sLinkHeader & sDatabaseToLink
        daotdfLink.SourceTableName = sTableToLink
        daodMainDatabase.TableDefs.Append daotdfLink
    Else
        'Table exists, edit link
        daotdfLink.Connect = sLinkHeader & sDatabaseToLink
        daotdfLink.RefreshLink
    End If
    
    'Done, exit good
    bDoTableLink = True
    Set daotdfLink = Nothing
    Exit Function


NoTable:

    'No table exists, set flag and resume
    bNoTable = True
    Resume Next


VeryBad:

    'An error occured, return false
    bDoTableLink = False
    Set daotdfLink = Nothing
    Exit Function

End Function

-JoeyCode