Results 1 to 7 of 7
  1. #1
    BilStenson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    2

    Issue: Creating hyperlinks in Access 2016

    This problem will be elementary I know but I'm stumped. I'm not a code writer but I understand access pretty well so I will explain as I understand it. I believe there is VB involved. I have a database, contains one table. This table contains nine fields, 1 autonum, 4 boolean, 1 date, 2 text and 1 hyperlink. One of the text fields contains 1750 unique records which are text strings, Examplefh_88991, fh_88892, etc). Each of these records should correspond to a .mp4 video contained within the same folder as the database. The file name of the videos contain the same record name, Examplefh_88991.mp4, fm_88992.mp4).

    The problem- I need to create 1750 hyperlinks to the videos in the hyperlink field and prefer not to do it one at a time. Here is what I want to do. I need the code to cause the hyperlink cell to: query the folder containing the videos, if a file is found that has the unique text record name (fm_88891=fm_88891.mp4) the file name (only the file name, no path information) is displayed in the cell as a click hyperlink. If no file is found containing the text string (fm_88891) the cell is left empty. Then replicate it 1749 more times.



    Help please, thank you in advance!!

    Bill

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put a button on the header of the form. when clicked, it opens the video without hyperlink.

    Code:
    sub btnShow_click()
    dim vPath
    dim sDb as string, sFilename as string
    
                         'get the db path 
    sDb = currentdb.Name
    i = InStrRev(sDb, "\")
    vPath = left(sDb,i)
    sFileName = vPath & txtVideoName
    
          'play the video
    OpenNativeApp sVideo
    end sub
    paste the code below into a module so the button click above will open the video.
    it will open ANY file in its native application.

    usage: OpenNativeApp ME.txtBox
    if the file is myFile.pdf, will open it in acrobat
    if the file is myFile.doc, it will open the doc in Word
    if its just a file path, it will open in file explorer.
    etc..


    Code:
    'Attribute VB_Name = "modNativeApp"
    'Option Compare Database
    Option Explicit
    
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, msg As String
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function

  3. #3
    BilStenson is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    2

    Almost there

    Ranman256,

    Thank you for your idea. Unfortunately I need to have the hyperlink"filename" in the cell. This was done for my client last year and they like the format. Unfortunately I am unable to replocate how it was done. I know in a round and about way how it was written, which is what I included in my post. Here is a look at what we have. I don't have a form perse. I do all of my work inside of the datasheet.
    Click image for larger version. 

Name:	Capture 2.JPG 
Views:	44 
Size:	89.1 KB 
ID:	27253
    Click image for larger version. 

Name:	Capture.JPG 
Views:	44 
Size:	99.8 KB 
ID:	27252

    The Vid_Link Field is the one containing the hyperlink, which is the filename.

    Any other thoughts?

    Bill

  4. #4
    Join Date
    Jun 2015
    Location
    Wales. Land of the sheep.
    Posts
    1,228
    http://www.brighthub.com/computing/w...les/59992.aspx

    specifically:

    Some Domain# http:// www.somedomain.com#

    the # symbol wraps the link and the text before is the display. (not tested though)

  5. #5
    Neil Bingham is offline Novice
    Windows 7 64bit Access 2000
    Join Date
    Feb 2009
    Location
    Cumbria UK
    Posts
    14
    Suggest you first make copy of dbase to try this out (it worked for me to make several thousand links to digital photo library in Access 2010)

    Create Hyperlink field in table
    Save table
    Create Query to update field with FULL file path (ie update the hyperlink field to ="C:\Records"&[file name]....etc)
    Run Query yo update table
    Open table
    in first record select the hyperlink field, double click - although filled with file path in blue it does not work does it? SO edit the hyperlink for that record (right mouse click etc) and select your full file path, save (but before closing make a note to check that file path you have selected is same as you have in your hyperlink field for ALL records - if not then run new query to amend it so that it is!) then check that the link works again
    Close table
    Open Table design
    Change field type from hyperlink to text
    Save & close
    Open table design again
    Change field type from text back to hyperlink
    Save & close
    Should now work
    Let me know if I doesn't! (I am sure you will!)
    If this works for you then no need to waste time and effort on devising code or macros

  6. #6
    Neil Bingham is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2009
    Location
    Cumbria UK
    Posts
    14
    Do you wish to create hyperlinks in bulk? (ie for hundreds of files or more) If so the fillowing might be of use to you:
    First create a table in your Access database with eight text fields - save it, and now you need to populate it (leave it open minimised)
    You of course need a list of file names with FULL FILE PATH - you can of course create by means of queries. I have found a very good freebie utility called "Bulk Rename Utility" -this has the facility also to extract full file path + some other file details of a complete folder (select folder - list of files appears, select all (Ctrl A) - or just the ones you need, click right mouse to see copy to clipboard options (extended file option will give eight fields) - then move to table and paste (ignore paste errors (it is the header row which it cannot paste)
    When you have copied all that you need from as many different folders that you wish, close table, open again in design view and change field type of the field containg the full filepath - probably field3- from text to hyperlink, save and open
    You now have a table with hyperlinks to all the files you wanted. You can of course now base queries, forms on it or copy or link the hyperlink field into another table etc
    The Bulk Rename Utility is a godsend - much easier to get bulk file names than Dir function in DOS
    And look, no hands! - ie no complex code or macros for the unitiated! - I have now created hyperlinks to around 10,000 digitsl photos (in a searchsble table that lists date taken, who is on it, and location. etc)
    Hope this helps those who wish to create bulk hyperlinks

  7. #7
    psmyth is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    1

    Smile

    This is most likely way too late for the original poster but may still help someone else, so here goes:

    In your case I would run the following update statement:
    [MH_NUM]&".MP4"#<insert path to folder containing video files here>"&[MH_NUM]&".MP4#"

    Say for instance that all the video files are found in the folder C:\Projects\Video Files, then the update statement would be:
    [MH_NUM]&".MP4"#C:\Projects\Video Files"&[MH_NUM]&".MP4#"

    Basically, the bit before the first # is the display name of your hyperlink and the bit between the #'s need to be a full valid path, UNC or http or otherwise, to the file or web page you want to open. The full path can be built in the query or updated from a separate table or field, the choice is yours.

    Quote Originally Posted by BilStenson View Post
    Ranman256,

    Thank you for your idea. Unfortunately I need to have the hyperlink"filename" in the cell. This was done for my client last year and they like the format. Unfortunately I am unable to replocate how it was done. I know in a round and about way how it was written, which is what I included in my post. Here is a look at what we have. I don't have a form perse. I do all of my work inside of the datasheet.
    Click image for larger version. 

Name:	Capture 2.JPG 
Views:	44 
Size:	89.1 KB 
ID:	27253
    Click image for larger version. 

Name:	Capture.JPG 
Views:	44 
Size:	99.8 KB 
ID:	27252

    The Vid_Link Field is the one containing the hyperlink, which is the filename.

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

Similar Threads

  1. Replies: 9
    Last Post: 06-16-2016, 07:54 PM
  2. Replies: 2
    Last Post: 04-19-2016, 04:09 PM
  3. Access 2016 Default Value Issue
    By breakingme10 in forum Forms
    Replies: 7
    Last Post: 09-23-2015, 10:07 AM
  4. Creating Hyperlinks in a report
    By baseborn in forum Reports
    Replies: 6
    Last Post: 01-25-2014, 03:08 PM
  5. Creating dynamic hyperlinks
    By mattellenburg in forum Access
    Replies: 1
    Last Post: 11-28-2011, 10:31 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