Relinking tables

[Susan]

I have a database that is split into a front end and a
back end. In certain instances, I want to copy both the
front end and the back end so that they may be used
independently of the REAL database that is on a network
drive. When I perform the copy, the tables in the front
end are still linked to the back end on the network drive
as expected. I want to programmatically change this so
that they are linked to the back end file on the local
drive.

I have the following code that I thought would work, but
I'm having no luck.

Dim DB as DAO.Database
Set DB = OpenDatabase(FrontEndDatabase)
For I = 0 To DB.TableDefs.Count - 1
If DB.TableDefs(I).Connect <> "" Then
DB.TableDefs(I).Connect = _
";DATABASE=" & NewBackEndDatabase
End If
Next I
DB.Close
Set DB = Nothing

I have tried various connect strings, but none have
worked. When I look in the debugger, the current string
has the format as above. When I execute this code, the
debugger shows that the connect string has changed. But
when I subsequently open the Access database, the tables
are still linked to the back end on the network.

Any suggestions?
Susan

 

[Brendan Reynolds \(MVP\)]

I think you need to refresh the link, Susan ...

If DB.TableDefs(I).Connect <> "" Then
DB.TableDefs(I).Connect = _
";DATABASE=" & NewBackEndDatabase DB.TableDefs(I).RefreshLink
End If

 

[Charles Coleman]

You need to call the .RefreshLink method. Try this:

Dim DB as DAO.Database
Dim tdf As DAO.TableDef
Set DB = OpenDatabase(FrontEndDatabase)
For Each tdf In DB.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = _
";DATABASE=" & NewBackEndDatabase
tdf.RefreshLink
End If
Next I
DB.Close
Set DB = Nothing
Set tdf = Nothing

Simply calling DB.TableDefs(I).RefreshLink may work too, but using an
object variable and looping through the collection (as above) is how
I've always done this.

 

[Susan]

Yes!!! Thank you, thank you, thank you!

-----Original Message-----
You need to call the .RefreshLink method. Try this:

Dim DB as DAO.Database
Dim tdf As DAO.TableDef
Set DB = OpenDatabase(FrontEndDatabase)
For Each tdf In DB.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = _
";DATABASE=" & NewBackEndDatabase
tdf.RefreshLink
End If
Next I
DB.Close
Set DB = Nothing
Set tdf = Nothing

Simply calling DB.TableDefs(I).RefreshLink may work too, but using an
object variable and looping through the collection (as above) is how
I've always done this.

"Susan" <[email protected]> wrote in
.

 

[Susan]

Yeah! It works!

Thanks a bunch!
Susan

-----Original Message-----
I think you need to refresh the link, Susan ...


--
Brendan Reynolds (MVP)
(e-mail address removed)




.