Solved

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

Posted on 2013-05-27
3
443 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

734 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