By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Code to change linked tables

P: n/a
I'm having trouble locating code to change the names/locations of
linked tables. For example, App1.mdb is linked to tblA in
w:\server1\data. I want the code to enable App1.mdb to relink to tblA
in e:\server1\data, without having to use the Linked Table Manager. Is
there a way to do this ? Any help is greatly appreciated.

Nov 22 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Something like:-

dim db as dao.database
dim tdf as dao.tabledef

set db = currentb

for each tdf in db.tabledefs
with tdf
if len(.connect) 0 then
.connect = "Your new connectstring here"
.refreshlink
end if
end with
next
set tdf = nothing
set db = nothing

BTW if you had bothered to search Google on the phrase Relink Access Tables
you would have got about 6000 hits, a large number of which would have
pointed you to similar code to the above.
--

Terry Kreft
"LucaBrasi" <Lu*******@FishNap.comwrote in message
news:4563d5f9.1267953@news-server...
I'm having trouble locating code to change the names/locations of
linked tables. For example, App1.mdb is linked to tblA in
w:\server1\data. I want the code to enable App1.mdb to relink to tblA
in e:\server1\data, without having to use the Linked Table Manager. Is
there a way to do this ? Any help is greatly appreciated.

Nov 22 '06 #2

P: n/a

Thank you for your help, I see that I have not actually asked the
question I wanted to. I wanted to link tables that are not all in the
same back end mdb. tblA is linked to a back end one a certain drive,
and tblB is linked to a back end on another drive. I have found plenty
of code segments to change all the links to the same new link, but not
individually. In effect, I'm looking for code like this:

tblA.newlink = "E:\Archive\History.mdb" ( This mdb contains tblA)
tblB.newlink = "S:\Public\Vendors.mdb" (This mdb conatins tblB)

So all the code I've found won't do that, and I'm not knowledgeable
enough to modify the code I've seen.
On Wed, 22 Nov 2006 09:25:43 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:
>Something like:-

dim db as dao.database
dim tdf as dao.tabledef

set db = currentb

for each tdf in db.tabledefs
with tdf
if len(.connect) 0 then
.connect = "Your new connectstring here"
.refreshlink
end if
end with
next
set tdf = nothing
set db = nothing

BTW if you had bothered to search Google on the phrase Relink Access Tables
you would have got about 6000 hits, a large number of which would have
pointed you to similar code to the above.
Nov 22 '06 #3

P: n/a
Ahh, I'll let you off then <g>.

Unfortunately you're reduced to doing something like the following

dim db as dao.database
dim tdf as dao.tabledef

set db = currentdb

Set tdf = db.Tabledefs("tblA")
with tdf
.connect = "Your new connectstring for tblA here"
.refreshlink
end with

Set tdf = db.Tabledefs("tblB")
with tdf
.connect = "Your new connectstring for tblB here"
.refreshlink
end with

'and so on ...

set tdf = nothing
set db = nothing

You can make this more generic to some extent by creating a table with two
fields one of which contains the tablename to relink and the other one
contains the new connection string.

e.g.
tblNewConnect
-----------------
TableName
ConnectString

You enter the details into this table and then you can do something like:-

dim db as dao.database
dim rs as dao.recordset
dim tdf as dao.tabledef
Dim strName as string
Dim strConnect as string

set db = currentdb
set rs = db.OpenRecordset("SELECT * FROM tblNewConnect, dbopensnapshot)

with rs
Do Until .EOF
strName = .Fields("TableName")
strConnect = .Fields("ConnectString")

Set tdf = db.Tabledefs(strName)
with tdf
.connect = strConnect
.refreshlink
end with

.Movenext
Loop
.Close
End With

set rs = nothing
set tdf = nothing
set db = nothing

--

Terry Kreft
"LucaBrasi" <Lu*******@FishNap.comwrote in message
news:45647216.2247953@news-server...
>
Thank you for your help, I see that I have not actually asked the
question I wanted to. I wanted to link tables that are not all in the
same back end mdb. tblA is linked to a back end one a certain drive,
and tblB is linked to a back end on another drive. I have found plenty
of code segments to change all the links to the same new link, but not
individually. In effect, I'm looking for code like this:

tblA.newlink = "E:\Archive\History.mdb" ( This mdb contains tblA)
tblB.newlink = "S:\Public\Vendors.mdb" (This mdb conatins tblB)

So all the code I've found won't do that, and I'm not knowledgeable
enough to modify the code I've seen.
On Wed, 22 Nov 2006 09:25:43 -0000, "Terry Kreft"
<te*********@mps.co.ukwrote:
Something like:-

dim db as dao.database
dim tdf as dao.tabledef

set db = currentb

for each tdf in db.tabledefs
with tdf
if len(.connect) 0 then
.connect = "Your new connectstring here"
.refreshlink
end if
end with
next
set tdf = nothing
set db = nothing

BTW if you had bothered to search Google on the phrase Relink Access
Tables
you would have got about 6000 hits, a large number of which would have
pointed you to similar code to the above.

Nov 23 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.