Need help with a query to alter data (1 Viewer)

[tragik]

Hello everyone, I am a complete noob with access and I am on access 2007.
I am building a database to archive my 10 years worth of magazines. This also includes scanning each article and a hyperlink to them from the local pc.

Here is my issue: Do to my lack of proper planning my image file structure is getting out of hand. My current folder structure is access/magazine/images/FS11_8/*.jpg (FS11_8 is the magazine FS, the volume 11 and issue 8). Now, with 10 years worth (3 different subcriptions, so 30 years worth) this is going to be quite large. I want to change this to access/images/FS/11/8/*.jpg, this way the magazine subcription will have it's own folder (seperating the 3), the volume would hold its issues rather than have one folder (/Images) hold 30 years worth of magazines (est: 300). See my goal. * I am only 38 magazine issues into this project and want to get this fixed before I go any farther.

Now to the point: I have several tables but I need help getting started to change the hyperlink field in a table through a query (maybe?)..
for example: Magazine table
[tblMagazine]
Magazine_ID;Magazine_Title;Magazine_Month;Magazine_Year;Volume;Issue;Cover_Image

Cover_Image is where i need to change the data.. I understand I may only be able to one magazine volume/issue at a time. Each volume/issue could have up to 40 articles i have logged.

Example: Cover_Image:Magazine\Images\FineScale15_7\FineScale15_7.jpg needs to be changed to Images\FS\15\7\Finescale15_7.jpg

Now, please could you direct me in the right direct to find this out? Do I need to build something like replace with Images\FS\15\7\* (* being something i dont want to change)

Even if it only can change that magazine issue (which could include 40 entries) it would faster than me re-directing the hyperlink.

I hope i make sense..

Thank you for your time.
sean

 

[HiTechCoach]

I have found the Hyperlink data type to be very difficult to work with.

You will need to use the HyperlinkPart and Repalce to update your data.

From the help file:

Public Sub DisplayHyperlinkParts(ByVal strTable As String, _
                                 ByVal strField As String)
    
    Dim rst As New ADODB.Recordset
    Dim strMsg As String

        
    rst.Open strTable, CurrentProject.Connection, _
             adOpenForwardOnly, adLockReadOnly

    ' For each record in table.
    Do Until rst.EOF
        strMsg = "DisplayValue = " _
            & HyperlinkPart(rst(strField), acDisplayedValue) _
            & vbCrLf & "DisplayText = " _
            & HyperlinkPart(rst(strField), acDisplayText) _
            & vbCrLf & "Address = " _
            & HyperlinkPart(rst(strField), acAddress) _
            & vbCrLf & "SubAddress = " _
            & HyperlinkPart(rst(strField), acSubAddress) _
            & vbCrLf & "ScreenTip = " _
            & HyperlinkPart(rst(strField), acScreenTip) _
            & vbCrLf & "Full Address = " _
            & HyperlinkPart(rst(strField), acFullAddress)
            
        ' Show parts returned by HyperlinkPart function.
        MsgBox strMsg
        rst.MoveNext
    Loop
    
End Sub
		
When you use the HyperlinkPart method in a query, the part argument is required. For example, the following SQL statement uses the HyperlinkPart method to return information about data stored as a Hyperlink data type in the URL field of the Links table:

SELECT Links.URL, HyperlinkPart([URL],0)
    AS Display, HyperlinkPart([URL],1)
    AS Name, HyperlinkPart([URL],2)
    AS Addr, HyperlinkPart([URL],3)
    AS SubAddr, HyperlinkPart([URL],4)
    AS ScreenTip
    FROM Links

 

[tragik]

Boyd, to be honest that is intimidating.. heheh.. i will try and pick at it to see if it works.. i do have a 'test' copy of my database so if i break it then all good.. thanks for the response so fast.. I will give it a shot.

 

[tragik]

Boyd, thank you for your code insight but due to my lack of Access knowledge and my lack of Skynet abilities i can not read my books on access fast enough to understand what you are showing me.. lol.. But, i have found a way to use the find/replace (ctrl f) and replace each record that way from each table. now, the image fields are not linked to any other tables (relationship) so I have no worries there. I have tested it on a backup and the hyperlink still works once find/replace is done. Thank you again. I will be doing that scale thing on ya.
good night.

 

[tragik]

ok boyd, the find/replace option only changes the display name.. not the hyperlink location.. could you help explain your code a little to me? thanks