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