Deliberately Breaking Linked Tables Using Code

[John Ortt]

I have a chunk of code which changes the links in a database to a new
folder.

The problem is that the "tbl.RefreshLink" produces an error if the new
location does not exist at the time of changing the link.

I wish to force the tables to relink to the non-existent database without
bringing up errors during the code.

I tried simply deleting the "tbl.RefreshLink" line but it doesn't update the
link location then.

Any help will be greatly appreciated, Thanks in advance,

John


Code Follows
////////////////////

Option Compare Database
Option Explicit

Function change_linksBreak()
Dim db As DAO.DATABASE
Dim tbl As DAO.TableDef
Dim oldBE As String
Dim newBE As String
Dim i As Integer
Dim tbln As String
Dim lnk As String

Set db = CurrentDb()

oldBE = "H:\Access\Local\New Folder\2\DATABASE.mdb"
newBE = "H:\DATABASE.mdb"

For i = 0 To db.TableDefs.Count - 1
tbln = db.TableDefs(i).Name
Set tbl = db.TableDefs(tbln)
lnk = tbl.Connect
If Left(lnk, 9) = ";DATABASE" Then
lnk = Replace(lnk, oldBE, newBE)
tbl.Connect = ""
tbl.Connect = lnk
' tbl.RefreshLink
End If
Next

End Function

Function Replace(ByVal Valuein As String, ByVal WhatToReplace As
String, ByVal Replacevalue As String) As String
Dim Temp As String, P As Long
Temp = Valuein
P = InStr(Temp, WhatToReplace)
Do While P > 0
Temp = Left(Temp, P - 1) & Replacevalue & Mid(Temp, P +
Len(WhatToReplace))
P = InStr(P + Len(Replacevalue), Temp, WhatToReplace, 1)
Loop
Replace = Temp
End Function

 

[Paul Overway]

What you're trying to do can't be done. The database must exist in the
location specified before using RefreshLink. So, you'll have to modify the
logic in your app. You might explain why you are trying to do
this...because it really makes no sense.

 

[Douglas J. Steele]

I don't believe it's possible to link to a non-existent database, nor do I
understand why you'd want to.

If you're trying to setup the frontend before you deliver to your customer
or users, put that code in and run it everytime the application starts.

 

[John Ortt]

I have inheritted an extremely complex database which updates tables in
approximately 15 other databases.

The autoexec macro in this database has approximately 1000 lines (including
sub-macros), each with subqueries and sections of code.

For the purpose of testing changes which I make I would like to break all
the links to folder "X" but leave the links to folder "Y" in place.

I would then know that it was impossible to overwrite the master data in the
"X" folder if I had missed a section of code.
Furthermore an error message would pop up if it couldn't find the table
which would act as a flag to let me know I hadn't removed the update.

(I hope that makes sense.....)

 

[John Ortt]

I have had a brainwave.....

Since all the update tables will rely upon the names of the tables being the
same I can update the tablenames instead of the link path.

If I get the code to prefix "DISABLED" at the front of the table name that
should achieve the same result.

Please let me know if my logic is flawed...

 

[Paul Overway]

If I'm understanding correctly, you're basically trying to establish a
testing environment. The solution is to create copies of the
production/master databases you're linking to...and link to the copies
instead. Your "brainwave" doesn't allow proper testing.