How to change drive letter in input form

[Vernon27]

Hello All,
I am using MS Access 2007 on a Windows 7 Home Premium desktop pc. I have an access database that list all the movies that I have in my collection. Included with the database are tables, queries and reports. In addition, the database listed any movie clips that I have on the pc’s hdd. In the input form is a field I called Location. That field will point to the location and name of the movie clip. When I built thie database years ago I had the movies stored on the E: drive in the Movies sub-directory (E:\Movies\xxx); where xxx represents the name of the movie.
I have since then purchased a new pc and the drive letters have changed; the movie clips are now stored on drive F: as that drive is a two terabyte drive.
Question: How to I chance the drive letter in my form from E:\Movies\xxx to F:\Movies\xxx; without having to manually change it in each field one at a time. I am looking for an "automated" process similar to the Find and Replace option in Word. It should be noted that the field is a hyperlinked field so when you click on the data in the field MS Access will look for the drive and the subfolder and then the name of the movie and it will start to play in MS Media Player.
Thanks in advance for your help.

Vernon

[pbaldy]

One way would be an update query using the Replace() function.

[Vernon27]

pbaldy, thanks for the quick reply. Will this the expression that I would use:

Replace("E:\Movies","E","F")

[pbaldy]

You can try, but I wouldn't.

That would also replace the e in movies. I'd do

Replace("E:\Movies","E:\","F:\")

In reality you're going to want a field name for the first argument, not a literal string.

[Vernon27]

Hmm, not sure how I will do that. As you can tell I am a novice at this.

[pbaldy]

You have a field in a table that contains these references, and you want to change them, right?

UPDATE TableName
SET FieldName = Replace(FieldName,"E:\","F:\")

should change all records in the table. Make a backup before you try it.

[Vernon27]

I have a field in the table called Location and it's in the Movies table.

So it looks like the expression will be:

UPDATE Movies
SET Location = Replace("E:\Movies","E:\","F:\")

[pbaldy]

Make sure you make a copy first. Like I said and showed, you likely want the field name in the Replace() function, not the literal string.

[Vernon27]

Ok, I will try it this weekend and let you know. Thanks for your direction.

[Vernon27]

Hello pbaldy,

I tried your query and it did not work. I created a new query and entered the expression and it still did not work. Do you know how I can enter the expression in a script maybe it will work then. Thanks for your help.

[pbaldy]

Can you post the SQL of your attempt? Or a sample db?

[amer]

I have the same problem here.... well I working with images not movies.

The fix you suggested looks a little different in my version of Access (2002)

I have a copy of 2007 put I have not updated it yet. I already moved my images to a new drive so I wanted to get everything working again before I switched to the new version.

I have this.

\\JOSE\Shared\the name of the image

and I need to change it to

R:\the name of the image

[amer]

I was able to change it using the replace command in the datasheet view but it only changed the text field and not the actual hyperlink.

[Jim Doherty]

As an aside to this for your info if you merely want to access an F Drive and make it appear as E without changing anything in your database then type the following into the run box......... SUBST command E: F:\

This will give you a virtual drive letter aliased

[Vernon27]

This is what I have in the Criteria section in the design view of the query.

Replace("E:\Movies\","E:\Movies\","C:\Users\Vernon 27\AppData\Files for Future Installations\Pictures and Movies\Movies\")