ODBC Linked Tables

[KC]

Is there any way to point the linked tables that I have to another source. I
have the same tables in a test database as I do in live, whilst developing
the database I pointed everything to test now it is doing what I want I want
it to look at live. Is there anyway to change the odbc datasource without
deleting and recreating the tables with the right odbc link ?

 

[Klatuu]

Use this:

Private Function ReLink(strNewPath As String) As Boolean
Dim dbs As Database
Dim tdf As TableDef
Dim intCount As Integer
Dim frmCurrentForm As Form

DoCmd.Hourglass True
On Error GoTo ErrLinkUpExit
Me.lblMsg.Visible = True
Me.cmdOK.Enabled = False

Set dbs = CurrentDb

For intCount = 0 To dbs.TableDefs.Count - 1
Set tdf = dbs.TableDefs(intCount)
If tdf.Connect <> "" Then
Me.lblMsg.Caption = "Refreshing " & tdf.NAME
DoEvents
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If ' tdf.Connect <> ""
Next intCount

Set dbs = Nothing
Set tdf = Nothing

DoCmd.Hourglass False
Me.lblMsg.Caption = "All Links were refreshed!"
ReLink = True
Me.cmdOK.Enabled = True
Exit Function

ErrLinkUpExit:
DoCmd.Hourglass False

Select Case Err
Case 3031 ' Password Protected
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is
password protected"
Case 3011 ' Table missing
DoCmd.Hourglass False
Me.lblMsg.Caption = "Back End does not contain required table '"
& tdf.SourceTableName & "'"
Case 3024 ' Back End not found
Me.lblMsg.Caption = "Back End Database '" & strNewPath & "'" & "
Not Found"
Case 3051 ' Access Denied
Me.lblMsg.Caption = "Access to '" & strNewPath & "' Denied" &
vbCrLf & _
"May be Network Security or Read Only Database"
Case 3027 ' Read Only
Me.lblMsg.Caption = "Back End '" & strNewPath & "'" & " is Read
Only"
Case 3044 ' Invalid Path
Me.lblMsg.Caption = strNewPath & " Is Not a Valid Path"
Case 3265
Me.lblMsg.Caption = "Table '" & tdf.NAME & "'" & _
" Not Found in ' " & strNewPath & "'"
Case 3321 ' Nothing Entered
Me.lblMsg.Caption = "No Database Name Entered"
Case Else
Me.lblMsg.Caption = "Uncaptured Error " & str(Err) &
Err.DESCRIPTION
End Select

Set tdf = Nothing
ReLink = False

End Function

 

[KC]

Thanks very much for the help. Saved me a lot of messing about