Link to a new table location

[Beth]

I am struggling with a linked table issue. My database has about 15 tables
and 1 of them (tblMembers) is linked to another database, which keeps
moving. I would like create some code that let's me specify the new data
path and it relinks the table to the new path. I am not very experienced
with VBA, so any help you can offer is appreciated.
Thanks,
Beth

 

[Douglas J. Steele]

There's sample code at "The Access Web" in
http://www.mvps.org/access/tables/tbl0009.htm but it'll need some tinkering,
as it's only set up to handle all tables being in the same database.

Post back if you have problems modifying it for your purposes.

 

[Beth]

Doug,
I am not very comfortable with VBA, so I tried to follow the code. I was
not able to make it work for a single table, tblMembers.
I retireve NewPathname from the main form where the code executes

Function RelinkTables(NewPathname As String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

With tblMembers
.Tdf.Connect = ";DATABASE=" & NewPathname
.Tdf.RefreshLink
End With

End Function

Can you explain what I am doing wrong?
Thanks,
Beth

 

[Douglas J. Steele]

Function RelinkTables(NewPathname As String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs

Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Set Tdf = Tdfs("tblMembers")

With Tdf
.Connect = ";DATABASE=" & NewPathname
.RefreshLink
End With

End Function

Or

Function RelinkTables(NewPathname As String)
Dim Dbs As Database
Dim Tdfs As TableDefs

Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

With Tdfs("tblMembers")
.Connect = ";DATABASE=" & NewPathname
.RefreshLink
End With

End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug,
I am not very comfortable with VBA, so I tried to follow the code. I was
not able to make it work for a single table, tblMembers.
I retireve NewPathname from the main form where the code executes

Function RelinkTables(NewPathname As String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs

With tblMembers
.Tdf.Connect = ";DATABASE=" & NewPathname
.Tdf.RefreshLink
End With

End Function

Can you explain what I am doing wrong?
Thanks,
Beth