Relinking tables

[tacieslik]

Wow! New forum decor, not been here for a while.

I have the following code which I'm hoping will allow me to update a linked table to the path stored in a table:

Option Compare Database

Function relink()

Dim dbs As Database
Dim tdf As TableDef
Dim strFileName As Path

strFilePath = DLookup("fldValue", "tblAdmin", "[fldItemName]='Host1Path'")

' Loop through all tables in the database.
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs

' If the table has a connect string, it's a linked table.
If Len(tdf.Connect) > 0 Then
tdf.Connect = ";DATABASE=" & strFileName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table. _

' If Error occurs HERE, on this line, -produces- <> 0
If Err <> 0 Then
RefreshLinks = False
Exit Function
End If
End If
Next tdf
RefreshLinks = True ' Relinking complete.


End Function

I get some red highlighted typo errors for lines 16 & 23 (If statements). I think it's due to the code '&lt' and '&gt'. I don't know if this is correct or not as I've just found most of the code in another thread (old thread). All I've added is the table lookup code at the top.

Thanks in advance.

 

[spacepro]

I know this isn't helping your code, but have you seen the sample db in the sample db forum that was posted which relinks tables.

Andy

 

[tacieslik]

Thanks SpacePro,

This may be a better solution to my problem so I'll have a look.

Cheers

 

[billyr]

A few years ago, I (perhaps clumsily) adapted the relinking module in "Northwind" to work with my (not linked) "tblTableLocs" table and "LocateTables" function. This module will also invoke the Windows file dialog box if the tables have for some reason been moved. I still have that code if you are interested. The moderators of this forum are the real pros; they could clean up my efforts and and the result could be a very usefull utility?

 

[tacieslik]

Hi Billyr,

I'd be grateful for a copy of your code.


Thanks, Tim

 

[Oldsoftboss]

Try http://www.access-programmers.co.uk/forums/showthread.php?t=63235

Dave

 

[tacieslik]

Thanks OldSoftBoss,

I'll have a look,

Regards,
TACIESLIK