Solved

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

Posted on 2013-05-27
3
397 Views
Last Modified: 2013-05-27
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
Comment
Question by:SoundBytes
  • 2
3 Comments
 
LVL 10

Accepted Solution

by:
plummet earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:SoundBytes
Comment Utility
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
 
LVL 10

Expert Comment

by:plummet
Comment Utility
You're very welcome, have a great day!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now