Recreate a database link

[Chalco]

I have some code that will re-link a table in the front-end to a back-end database and that works OK, however, I now find myself needing to recreate a link to a backend access database which in turn links to a sql-server database.

I have managed to create the link programatically, and whilst the access database is open following the linking, I can select from the linked tables no problem, however, if I close the front-end and then re-open, I cannot read from the table. - Any ideas ?

Situation.
Another depatment supplies a database which is read-only to the underlying sql-server database, but it delivers new versions without notice (deleting the old version)

My application, on startup, check to see if it can read from the supplied database, if not, it prompts the user to re-link to the database.

code as follows :-
Public Sub Link_Tables(sSourcePath As String, sTargetPath As String)
'
' Re-Attaches all tables in Source database into current database.
'----------------------------------------------------------------------------------------------------
Dim dbSrc As Database
Dim dbTarg As Database
Dim tdSrc As TableDef
Dim tdTarg As TableDef

On Error GoTo ErrHandler

Set dbSrc = OpenDatabase(sSourcePath, False, True)
Set dbTarg = CurrentDb

For Each tdSrc In dbSrc.TableDefs
If tdSrc.Name = "dbo_vProject" Or _
tdSrc.Name = "dbo_vWeeksInfo" Then
'Remove old table
Call KillTable(tdSrc.Name, sTargetPath)
'
' check if the table is a link table or real table
'
If tdSrc.Connect = "" Then
'
' real table
'
'Re-Attach New Table
Set tdTarg = dbTarg.CreateTableDef(tdSrc.Name)
tdTarg.Connect = ";DATABASE=" & sSourcePath
tdTarg.SourceTableName = tdSrc.Name
dbTarg.TableDefs.Append tdTarg
Set tdTarg = Nothing
Central_DB_Exists = True
Else
'
' Link table
'
'Re-Attach New Table
Set tdTarg = dbTarg.CreateTableDef(tdSrc.Name)
tdTarg.Connect = tdSrc.Connect
tdTarg.SourceTableName = tdSrc.Name
tdTarg.SourceTableName = tdSrc.SourceTableName
dbTarg.TableDefs.Append tdTarg
Set tdTarg = Nothing
Central_DB_Exists = True
End If
Else
'
' Ignore
'
End If
Next tdSrc

WrapUp:
Set tdTarg = Nothing
Set tdSrc = Nothing
dbTarg.Close: Set dbTarg = Nothing
dbSrc.Close: Set dbSrc = Nothing
Exit Sub

ErrHandler:
MsgBox "Link_Table Error - " & Err & " description = " & Err.Description
GoTo WrapUp

End Sub