Results 1 to 5 of 5
  1. #1
    denniston is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    6

    Linking External Files

    I'm trying to associate files with records. From what I've read, it's not a great idea to actually embed the files into the db.

    Since I may have multiple files to associate with each record, created a table to hold the attachment records names Attachments.



    Code:
    ID | ClientID | File
    Questions #1
    Is it possible to have the database copy the file from the original location a new predefined folder for holding database linked files?

    Question #2
    I have a subreport on my form that lists the records from a query of the Attachments table. I would some sort of a option to add attachments to the record directly under the sub report. Is this best accomplished with a button to open a popup form? Is there a way to have it be text that is clicked on instead of a button?

    Question #3
    I am having trouble creating the popup from to add the record to the Attachments table. I don't know what kind of control to use to format the hyperlink field.

    Thank You

    Jason

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    it's not a great idea to actually embed the files into the db.
    as long as you're always talking about an Access db. Not true for all types. Anyway,
    1) yes; if the target or source folder is not static, common approach is to use the MSOFileDialogFilePicker to choose the file. Need to use vba code for this task.

    2)
    subreport on my form
    Bit confusing. Sub reports usually go on reports; sub forms on forms. Can't say I've ever heard of mixing the two. You want to add a record to a table and the record has a field that is the network path of the attachment? See #1. When you navigate to the folder (source or desination, doesn't matter) and choose the file, the picker returns the path. You write the path to the table. A button would be the most common method. You can format its background to be transparent, showing only the button caption.

    3) you don't need an additional form just to add the attachment path (we are talking about only writing the path to the table?) as already covered. The textbox control showing the path can be formatted to hyperlink (property sheet, format tab) but if the table field is of the hyperlink type, you shouldn't have to worry about the form control.

    If the subform (I'm guessing that's what you meant) is a continuous form you could have a button that will show for every record, and you can differentiate between one record and another quite easily in order to invoke the file dialog to find the related document. Some might not like the look of a button on every record.
    Last edited by Micron; 12-10-2017 at 10:19 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Be aware that "File" is a reserved word. (and "ID" is a poor field name)

    Since I may have multiple files to associate with each record, created a table to hold the attachment records names Attachments.
    I wouldn't use an Attachment type field.

    I would have field names
    FileID_PK (Autonumber)
    ClientID_FK (Long)
    FilePathName (Text/Hyperlink)

    Questions #1
    Is it possible to have the database copy the file from the original location a new predefined folder for holding database linked files?
    I read this a little different. Are you asking if you can have a button to copy/move a file from "C:\Users\userName\Downloads" to "C:\Clients\Client1\DataFolder"?
    Yes, you can... with some VBA.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think we're saying the same thing in a different way. My suggestion to start with the file picker is to navigate to the file for copying. "Need to use vba code for this task" is shorthand for "vba code required to find and copy the file - the whole enchilada"
    Good catch on the reserved words. Sometimes I'm in such a hurry to answer before you that I miss stuff.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by Micron View Post
    <snip> Sometimes I'm in such a hurry to answer before you that I miss stuff.
    Now that is funny. Thanks, I needed some humor....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. uae external files
    By Jen0dorf in forum Access
    Replies: 7
    Last Post: 11-21-2015, 03:44 PM
  2. Link to external files
    By spacekowboy in forum Access
    Replies: 5
    Last Post: 06-08-2015, 08:09 PM
  3. Linking to thousands of external images
    By taichi56 in forum Access
    Replies: 5
    Last Post: 06-03-2013, 11:24 PM
  4. Really need help with external excel files
    By zu3st3r in forum Import/Export Data
    Replies: 6
    Last Post: 02-04-2012, 02:48 AM
  5. Error Linking External Table
    By mcgowan.b in forum Import/Export Data
    Replies: 9
    Last Post: 11-24-2009, 07:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums