Prev: Missing Operator
Next: new messenger
From: cyb3rwolf on 8 Mar 2010 14:51
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)
From: Marshall Barton on 8 Mar 2010 15:50
cyb3rwolf wrote:

>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.

--
Marsh
MVP [MS Access]
From: cyb3rwolf on 8 Mar 2010 17:34
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?

"Marshall Barton" wrote:

> cyb3rwolf wrote:
>
> >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.
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Marshall Barton on 8 Mar 2010 18:25
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]


cyb3rwolf wrote:
>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?
>
>"Marshall Barton" wrote:
>> cyb3rwolf wrote:
>> >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.
 | 
Pages: 1
Prev: Missing Operator
Next: new messenger