Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2017
    Posts
    1,673

    Saving/Using URL/Hyperlink In SQL Database with MS Access Front End

    Lately I am designing a SQL Server DB, which will be used through MS Access FE. One feature I'm not familiar currently is managing links (at least in those environments) to various types of documents saved on some shared resource in LAN (and maybe on some online resources too). So I'll be thankful when anyone can give some guidelines about this.



    As much as I got, In SQL Server I have to use nvarchar type field to store any links (or varchar, but as there is possibility for e.g. file names having non-english characters, this will be probably not an option). When I have a table with such field linked to MS Access FE, it will have the field type Short/Long Text (depending on length of nvarchar/varchar field). At same time, there is a Hyperlink field type available in Access. Is there a possibility (and/or a need) to change the field type in FE?

    There will be several different types of documents linked to different objects (request documents, article documents, supplier documents, customer documents, etc.). I think mostly we will store those documents on different subfolders of some documents folder in some shared resource. Any links to documents bound to any object are managed in subforms linked to form managing all objects of given type (i.e. Articles form has subform to link article type documents to active article). Is there a way to save the path part of URL automatically unless it is entered/selected manually (e.g. some internet resource is linked instead)? I mean when user wants e.g. to link a document with specific article, he/she doesn't need to navigate manually to folder with article documents, but the folder will be opened automatically, and the user has only to select correct document from there).

    In Word or Excel, when I create a link to some document, I can set some meaningful info to be displayed instead of URL. Can I do this in Access FE too?


    Thanks in advance for any tips!
    Arvi Laanemets

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot change field types in FE.
    Instead of hyperlinks, you can use this alternate method of just having the file path in a field. The code below will open any document in its native application.

    .pdf files will open in acrobat,
    .doc files in word
    etc

    USAGE:
    OpenNativeApp txtBox


    Code:
    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
    Join Date
    Apr 2017
    Posts
    1,673
    OK! I'll check this out!

    What about registering the link without copy-pasting the URL? The only idea I have currently is to have a button which opens File Selection Dialog window, and saving the path of selected file into field of active row. Is this a way to go?

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    With specific regard to the hyperlinks the other option would be to store the URL in one nvarchar field , and the Text description in another.

    In the front end you could then display the text description (underlined and in blue to look like a hyperlink) , and simply open the URL in the background when the "URL text" is clicked.
    You can even change the mouse pointer to a hand.

    @Ranmans code will doe the rest for other types of document, file links.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Minty View Post
    With specific regard to the hyperlinks the other option would be to store the URL in one nvarchar field , and the Text description in another.
    Had this in this way even before I posted this thread

    I probably will also have a Setup table in SQL Database, where I can set URL-strings for various document types (the number of types is fixed). So clicking on button in subform (e.g. button GetDocument) opens Open File dialog in proper default directory, but user can always navigate to any other source when there is a need.

    Another question here - is a separate button needed in case the source will be in internet instead of LAN? Until now I never needed this, and it looks web address doesn't work in File Picker window!

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In my limited experience, if you use application.followhyperlink it generally works it out for you.

    You could always check if the path existed as a UNC first if not direct it to the browser?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 9
    Last Post: 04-01-2014, 09:28 PM
  2. Replies: 1
    Last Post: 03-26-2014, 09:28 AM
  3. Replies: 2
    Last Post: 11-05-2013, 09:00 AM
  4. Saving a hyperlink into my database
    By Jan22 in forum Access
    Replies: 1
    Last Post: 05-25-2012, 02:39 PM
  5. Link SQL Database to new Access front end
    By gumbi17 in forum SQL Server
    Replies: 3
    Last Post: 02-07-2012, 10:07 PM

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