Solved

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

Posted on 2013-05-27
3
438 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

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