Solved

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

Posted on 2013-05-27
3
411 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
ID: 39199708
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
ID: 39199717
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
ID: 39199794
You're very welcome, have a great day!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article will show you how to use shortcut menus in the Access run-time environment.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

932 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

14 Experts available now in Live!

Get 1:1 Help Now