Update hyperlink path

[jobbie]

I had created an access database to store path names of files on my local drive using the below vba code:

Dim rst As dao.Recordset



Set rst = MyDB.OpenRecordset("MyTable", dbOpenDynaset, dbAppendOnly)

With rst
. AddNew
! [File_path] ="Link" & "#c:\myfiles#"
.Update

rst.Close


I have only mentioned the code relevant to my question and have left out the other details. The code works fine and I have updated more than 2000 records using it but now I want to change the path of the hyperlink from "#c:\myfiles#" to "#c:\thosefiles#"
How do I do so?

Thanks

[isladogs]

It's tempting to say just modify the !filepath line accordingly but of course you're simplifying to help readers

However, in trying to be helpful, you've removed too much info to give a sensible answer

[jobbie]

It's tempting to say just modify the !filepath line accordingly but of course you're simplifying to help readers

However, in trying to be helpful, you've removed too much info to give a sensible answer

Thanks for your reply but honestly it's really a simple question. I just want to know how can I replace "my files" with "thosefiles" in the path for those 2000 records? I had used a textbox to input data and then used a loop to update the rows in the table based on certain criteria. I am unable to post the entire code because my office computer has internet blocked. I am typing this message from my mobile phone. I can't update the records through access manually because the table displays "Link" instead of the file name.

[isladogs]

It would be a simple question ... once your meaning is clear to anyone else

I just want to know how can I replace "my files" with "thosefiles" in the path for those 2000 records?

Surely you don't really mean this which is how both posts read:

![File_path] ="Link" & "#c:\thosefiles#"

I can't update the records through access manually because the table displays "Link" instead of the file name.

Please explain why that is the case. If you are using lookups at table level, that's a bad idea as it obscures the real data

My solution would be to use a text field to hold the link path instead of a hyperlink field
Then you would just reference the path in your recordset line

[jobbie]

Surely you don't really mean this which is how both posts read:

![File_path] ="Link" & "#c:\thosefiles#"

Yes that's correct

Please explain why that is the case. If you are using lookups at table level, that's a bad idea as it obscures the real data

My solution would be to use a text field to hold the link path instead of a hyperlink field
Then you would just reference the path in your recordset line[/QUOTE]

I wanted a link instead of the path name to be displayed because I have another form which would allow users to click on the link to take them to the file. Now I just want to change the path name of all the files.

[isladogs]

Ok it's your database and not mine.
Suggest you do a backup first in case I've misunderstood you....

Then use this code

![file_path]=Replace([file_path],"my files","thosefiles")

Or scrap all your code and use an update query which will be MUCH faster
In the update to row of the file path field enter the Replace code above

Note that you wrote myfiles without a space in post 1 but added a space in post 3. Use whichever is correct.

[jobbie]

Ok it's your database and not mine.
Suggest you do a backup first in case I've misunderstood you....

Then use this code

![file_path]=Replace([file_path],"my files","thosefiles")

Or scrap all your code and use an update query which will be MUCH faster
In the update to row of the file path field enter the Replace code above

Note that you wrote myfiles without a space in post 1 but added a space in post 3. Use whichever is correct.

Thanks it worked!

[isladogs]

Excellent.
For future reference, update queries are always faster than looping through recordsets one record at a time.
If you have many records, the difference can be dramatic.