Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 457
  • Last Modified:

Access 2007 Hyperlink Field in Table - Copy document to new folder

I have a hyperlink field in a table.  I want to be able to COPY the referenced hyperlinked file to a NEW Folder, and leave the old folder and file intact.  I can update the hyperlink reference in the table once it is moved to a new folder.  This is not a one time move.  It will occur many times per day.  I have a field in my current table that indicates whether the item has been moved already.  

I would like to run a query that indicates those items that have not been copied to the new folder, then run some code that would allow the files to be copied all at once.  They are all moving to a single folder, so there is no guesswork:

\\crsql\BossAttachments\NewAttachments\*.*

Any help to make this as easy as possible would be appreciated
0
SoundBytes
Asked:
SoundBytes
  • 2
1 Solution
 
plummetCommented:
How about something like this. I have used a table name "tblHLink" and have made up some field names and file paths, but you should be able to adjust it for your own structure easily:

Sub moveStuff()

    Dim rsStuff As Recordset
    Dim sFile As String
    
    Set rsStuff = CurrentDb.OpenRecordset("select * from tblhlink")
    
    Do While Not rsStuff.EOF
    
        With rsStuff
            If .Fields("Copied") = False Then
                sFile = Replace(.Fields("hlink").Value, "#", "")
                FileCopy sFile, "c:\temp\newstuff\" & Mid$(sFile, InStrRev(sFile, "\") + 1)
                .Edit
                .Fields("Copied").Value = True
                .Update
            End If
            .MoveNext
        End With
    Loop
    
    rsStuff.Close
    
End Sub

Open in new window


Cheers,
John
0
 
SoundBytesAuthor Commented:
Cheers and hurrahs John!  Made my memorial day.  Might get to eat some burgers and dogs after all!  Appreciate your quick and brilliant response!!!
0
 
plummetCommented:
You're very welcome, have a great day!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now