Change paths to linked external data

[cyb3rwolf]

Hello. I have a database that has 3 external links in it: 1 is to a table
in another Access Database. One is to an excel spread sheet. And, the third
is to a .csv file. Is there a way in VBA to change the paths to these?
Basically, i want to set up a form where the user can specify the new paths
in text boxes if any of these files are moved. Anybody help me out? (Access
2007)

 

[Marshall Barton]

Hello. I have a database that has 3 external links in it: 1 is to a table
in another Access Database. One is to an excel spread sheet. And, the third
is to a .csv file. Is there a way in VBA to change the paths to these?
Basically, i want to set up a form where the user can specify the new paths
in text boxes if any of these files are moved. Anybody help me out? (Access
2007)

That information is in the linked table's Connect property:

To see what the connect property looks like for your linked
tables, use the Immediate window:
?CurrentDb.TableDefs![table name].Connect

Once you understand what's in the Connect property (it's
different for each kind of file), you can write code to
(re)construct it with a different path. The path\file is
immediately after ;Database= and the rest of the connect
string needs to be the same as you currently have.

 

[cyb3rwolf]

This worked to view the properties on each one. I still can't figure out how
to change the properties with vb code, though (new to vba). Anybody able to
assist me further?

Hello. I have a database that has 3 external links in it: 1 is to a table
in another Access Database. One is to an excel spread sheet. And, the third
is to a .csv file. Is there a way in VBA to change the paths to these?
Basically, i want to set up a form where the user can specify the new paths
in text boxes if any of these files are moved. Anybody help me out? (Access
2007)

That information is in the linked table's Connect property:

To see what the connect property looks like for your linked
tables, use the Immediate window:
?CurrentDb.TableDefs![table name].Connect

Once you understand what's in the Connect property (it's
different for each kind of file), you can write code to
(re)construct it with a different path. The path\file is
immediately after ;Database= and the rest of the connect
string needs to be the same as you currently have.

 

[Marshall Barton]

If you can guarantee the rest of the connect string, you can
use the Left function to get the part that doesn't change:

Dim strNewPath As String
strNewPath = ???
With CurrentDv.TableDefs![your table]
.Connect = Left(.Connect, N) & strNewPath
End With

OTOH, it would be more relable/versatile to look for the
;DATABASE= part (always at the end?)

Dim intPos As Long
. . .
strFirstPart = Left(.Connect, ";DATABASE=")
.Connect = Left(.Connect, strFirstPart - 1) & ";DATABASE="
& strNewPath

Be sure to check VBA Help for anything you are not already
familiar with.
--
Marsh
MVP [MS Access]

This worked to view the properties on each one. I still can't figure out how
to change the properties with vb code, though (new to vba). Anybody able to
assist me further?

Hello. I have a database that has 3 external links in it: 1 is to a table
in another Access Database. One is to an excel spread sheet. And, the third
is to a .csv file. Is there a way in VBA to change the paths to these?
Basically, i want to set up a form where the user can specify the new paths
in text boxes if any of these files are moved. Anybody help me out? (Access
2007)

That information is in the linked table's Connect property:

To see what the connect property looks like for your linked
tables, use the Immediate window:
?CurrentDb.TableDefs![table name].Connect

Once you understand what's in the Connect property (it's
different for each kind of file), you can write code to
(re)construct it with a different path. The path\file is
immediately after ;Database= and the rest of the connect
string needs to be the same as you currently have.