Batch Update Hyperlinks in Access Table

March 11, 2010 10:17 AM

How can I update a batch of hyperlinks in an Access Database table?

We're running Office 07 here at work, and our IT guy is getting ready to move a big chunk of our files to a new drive space. This is all well and good, but there is an Access Database that links to these individual files.

The names of each file are in a Access Table, and those names have been hyperlinked to the actual file. Find and Replace isn't working... I assume because the text of the hyperlink doesn't register. Is there any way I can fix these links without going in one by one? We're talking years worth of files here.

The IT guy is very nice, but his fixes tend to be overwrought, overcomplicated and more trouble than they're worth. (One time I asked him to add a link to a file to our company website, and he started talking web design software)

I've had a few classes in Access, but I'm still a total newbie. Any help is really appreciated!

posted by Caravantea to Computers & Internet (3 answers total)

Best answer: You can do this easily enough with SQL, e.g., if the old hyperlinks look like this:

http://www.myserver.com/somedirectory/somefile.doc

You can change all the hyperlinks by running a query similar to this:

update MyTable 
set Link = replace(Link, 
    'http://www.myserver.com/somedirectory/',
    'http://www.myserver.com/someotherdirectory/')

where MyTable is the name of the table holding the hyperlinks , and Link is the name of the column with the hyperlinks in it.
posted by SNACKeR at 11:03 AM on March 11, 2010

Oh, and make sure you backup the database before running an UPDATE statement like this, it is easy to get things wrong!
posted by SNACKeR at 11:06 AM on March 11, 2010

Best answer: Thank you!

I also ended up finding another way though a non-Metafilter friend. By changing the column in the table from data type 'hyperlink' to 'text' which made the link location visible. Then I could do a Find and Replace and change the data type back to hyperlink.
posted by Caravantea at 11:25 AM on March 11, 2010