Re:linking files

[Access Beginner]

Re:linking files

Hi All,

I have an Access Database that links to various text and excel files. The path is located on a server P:\Data. Due to changes with our IT structre this server has changed to Z:\Data. Is there a way to change all links without going into each link individually?

Using Access 2010.

Any advice is appreciated.

Cheers
Haydn

 

[revver]

Re: linking files

To do this properly you should change every instance but there are two other things you could do.

If drive letter P is still available on the workstations (and it probably is since it was in use previously) you could use the subst command to equate Drive P: with the new Z: and everything should just work again.

I assume all references to the excel and text files appear only in VBA. You could use a global string variable and set it to "Z:\Data".
If there are link references that are outside VBA environment then you could the TempVars collection to set a global value for the string variable there. You still must change every link to use the new variable name but if this ever happens again you only need change the value of the variable.

 

[xenou]

Re: linking files

For linked tables you can use some code like this.

[COLOR="Navy"]Sub[/COLOR] LinkTables()
[COLOR="Navy"]Dim[/COLOR] tdf [COLOR="Navy"]As[/COLOR] TableDef

    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] tdf [COLOR="Navy"]In[/COLOR] CurrentDb.TableDefs
        [COLOR="Navy"]Debug[/COLOR].[COLOR="Navy"]Print[/COLOR] tdf.Connect
        tdf.Connect = Replace(tdf.Connect, "P:\", "Z:\")
        tdf.RefreshLink [COLOR="SeaGreen"]' Re-link the table.[/COLOR]
    [COLOR="Navy"]Next[/COLOR] tdf
    [COLOR="Navy"]Set[/COLOR] tdf = [COLOR="Navy"]Nothing[/COLOR]

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Backup first. You might want to step through the code and/or run it with only the debug.print statement first so you can see how many tables and what their paths look like.

 

[Access Beginner]

Re: linking files

thanks for the response I'll look into it much appreciated. I'll test it out on Monday when I'm back at work.

Cheers

 

[Access Beginner]

Re: linking files

thanks for the response revver, much appreciated. I'm back at work on Monday and I'll keep this in mind in the future