Changing Backends (XP)

Topic

New Reply

I need a simple little procedure, that I can pass the full path to, that will update the table links to a
eg RenewTableLinks(“c:datanewbackend.mdb”)

Reply | Quote

Replies

Try this:

Sub RenewTableLinks(strBE As String)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
On Error GoTo ErrHandler
For Each tdf In dbs.TableDefs
If Not tdf.Connect = "" Then
tdf.Connect = ";DATABASE=" & strBE
tdf.RefreshLink
End If
Next tdf
ExitHandler:
Set tdf = Nothing
Set dbs = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

Note: you must set a reference to the Microsoft DAO 3.6 Object Library in Tools | References…

Reply | Quote

I searched using google and found one that was quite good but it looked a bit old – (it looked at all objects not just tabledef) your’s is much better. Very slick and efficient.

I just modified it to be a function and return true if all went well.

Your’s still uses DAO specifically so it will work for my one client that is still using office 97.

thank you

Reply | Quote

> I just modified it to be a function

LOL – I had a function lying around for this, but you asked for a procedure so I modified it to be a procedure

Reply | Quote

I didn’t know ‘procedure’ meant sub. but now I do. thanks again.

Reply | Quote