Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Mar 2012
    Posts
    17

    Unanswered: Creating / Veiwing Hyperlinks to files in folders

    Hi..was wondering if it was at all possible to do this..

    I needed a simplistic way of exporting emails into access for a workflow system - cannot do this directly.

    To gain workflow volumes I am able to save the emails as a PDF and then save these to a folder... I am now looking to create a new record in Access which links to this pdf file via hyperlink, this will create workflow volume and allow person to view email content.

    I have a few issues....

    How can I pick up the file names "*.pdf's" and export these file names into a hyperlink field in the database and then for the database to understand that these files have been imorted so not to import them in the future..

    I'm pretty stumped at the moment...anyone got any wisdom they can part with

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a fast solution.

    1. The names of the files are imported into the table Tbl_FileNames which is used as an input buffer.
    2. An INSERT query inserts the data (names and links) into a second table (Tbl_Links in my example) for the names that are in Tbl_FileNames but not in Tbl_Links (i.e. the new files that are not already present in Tbl_Links).
    3. The columns Tbl_FileNames.FileName and Tbl_Link.FileName are indexed (PRIMARY KEY, at least for Tbl_FileNames.FileName) to get the maximum performance from the INSERT query.
    4. The constant c_Path contains the full name of the folder that the procedure must explore ("U:\Access\" in my example). It can be changed or replaced by a parameter passed to the procedure.
    Code:
    Sub ImportNewFiles()
    
        Const c_Path As String = "U:\Access\"
        Const c_SQL0 As String = "CREATE TABLE Tbl_FileNames ( FileName TEXT(128) CONSTRAINT PK_Tbl_FileNames PRIMARY KEY)"
        Const c_SQL1 As String = "INSERT INTO Tbl_FileNames ( FileName ) VALUES ( '@N')"
        Const c_SQL2 As String = "INSERT INTO Tbl_Links ( FileName, FileLink ) SELECT a.FileName, '@P' & a.FileName " & _
                                 "FROM ( SELECT Tbl_FileNames.FileName " & _
                                        "FROM Tbl_Links RIGHT JOIN Tbl_FileNames ON Tbl_Links.FileName = Tbl_FileNames.FileName " & _
                                        "WHERE Tbl_Links.FileName Is Null " & _
                                      ") AS a;"
        
        Dim strFileName As String
        
        If DCount("*", "MSysObjects", "Name = 'Tbl_FileNames'") = 0 Then
            CurrentDb.Execute c_SQL0, dbFailOnError
        Else
            CurrentDb.Execute "DELETE FROM Tbl_FileNames;", dbFailOnError
        End If
        strFileName = Dir(c_Path & "*.mdb")
        Do While Len(strFileName) > 0
            CurrentDb.Execute Replace(c_SQL1, "@N", strFileName), dbFailOnError
            strFileName = Dir
        Loop
        CurrentDb.Execute Replace(c_SQL2, "@P", c_Path), dbFailOnError
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    17

    Thanks Sinndho ..1 more question

    Hi Sinddho, many thanks for your swift response

    Adapted your code to look at my directory but having an issue with the follwing line

    CurrentDb.Execute Replace(c_SQL1, "@N", strFileName), dbFailOnError

    looks not to be defined..

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you mean with 'looks not to be defined"? c_SQL1 is defined on the 3rd line of the procedure:
    Code:
        Const c_SQL1 As String = "INSERT INTO Tbl_FileNames ( FileName ) VALUES ( '@N')"
    Do you receive an error when executing the code? If yes, please post the error number and the full error message.

    You can also stop (F9) the code on the line:
    Code:
    CurrentDb.Execute Replace(c_SQL1, "@N", strFileName), dbFailOnError
    and see (in the Immediate Window) what's in the expression:
    Code:
    ? Replace(c_SQL1, "@N", strFileName)
    Have a nice day!

  5. #5
    Join Date
    Mar 2012
    Posts
    17
    Hi Sinndho

    I've attached the screen dump, I'm probably missing something really simple so forgive me for looking slightly dumb
    ScreenDump.doc

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Which version of Access do you use?
    Have a nice day!

  7. #7
    Join Date
    Mar 2012
    Posts
    17
    Access 97 - I'm guessing this could possibly be the reason

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Indeed! The function Replace() does not exist in Access 97. Here's an alternative solution:
    Code:
    Sub ImportNewFiles()
    
        Const c_Path As String = "U:\Access\"
        Const c_SQL0 As String = "CREATE TABLE Tbl_FileNames ( FileName TEXT(128) CONSTRAINT PK_Tbl_FileNames PRIMARY KEY)"
        
        Dim strFileName As String
        Dim strSQL As String
        
        If DCount("*", "MSysObjects", "Name = 'Tbl_FileNames'") = 0 Then
            CurrentDb.Execute c_SQL0, dbFailOnError
        Else
            CurrentDb.Execute "DELETE FROM Tbl_FileNames;", dbFailOnError
        End If
        strFileName = Dir(c_Path & "*.mdb")
        Do While Len(strFileName) > 0
            strSQL = "INSERT INTO Tbl_FileNames ( FileName ) VALUES ( '" & strFileName & "');"
            CurrentDb.Execute strSQL, dbFailOnError
            strFileName = Dir
        Loop
        strSQL = "INSERT INTO Tbl_Links ( FileName, FileLink ) " & _
                 "SELECT a.FileName, '" & c_Path & "' & a.FileName " & _
                 "FROM ( SELECT Tbl_FileNames.FileName " & _
                        "FROM Tbl_Links RIGHT JOIN Tbl_FileNames ON Tbl_Links.FileName = Tbl_FileNames.FileName " & _
                        "WHERE Tbl_Links.FileName Is Null " & _
                      ") AS a;"
        CurrentDb.Execute strSQL, dbFailOnError
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Mar 2012
    Posts
    17
    Wow..Thanks for your support with this Sinndho, Nearlly there

    I'm getting a syntax error, I've attached another screen dump.

    If I've followed your instructions right, I've created a table called Tbl_Links with 2 fields called FileName and FileLink..As you mentioned Tbl_FileNames will be created upon the code running...

    Once again thanks for your help with this...ur a legend

    ScreenDump2.doc

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what does the actual SQL you are sending to the SQL engine look like
    not the VBA that creartes the SQL but the ACTUAL SQL, the value of strSQL.
    either display it as a msgbox or put a watch / breakpoint ont he code and examine the value.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2012
    Posts
    17
    Hi,

    looking at whats being passed across I can see

    "INSERT INTO Tbl_Links ( FileName, FileLink ) SELECT a.FileName, 'Path of Folder' & a.FileName FROM ( SELECT Tbl_FileNames.FileName FROM Tbl_Links RIGHT JOIN Tbl_FileNames ON Tbl_Links.FileName = Tbl_FileNames.FileName WHERE Tbl_Links.Fl"

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How did you define then constant c_Path?
    Have a nice day!

  13. #13
    Join Date
    Mar 2012
    Posts
    17
    hi Sinndho

    Here path to test folder I've created that includes pdf files

    Const c_Path As String = "C:\Test Folder\"

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The how come we find:
    Code:
    SELECT a.FileName, 'Path of Folder' & a.FileName
    in the string passed to the Jet Engine?

    Please post the the procedure as you have it in text (not a screen snapshot).
    Have a nice day!

  15. #15
    Join Date
    Mar 2012
    Posts
    17
    Here is what I've got

    Sub ImportNewFiles()

    Const c_Path As String = "C:\Test Folder\"
    Const c_SQL0 As String = "CREATE TABLE Tbl_FileNames ( FileName TEXT(128) CONSTRAINT PK_Tbl_FileNames PRIMARY KEY)"

    Dim strFileName As String
    Dim strSQL As String

    If DCount("*", "MSysObjects", "Name = 'Tbl_FileNames'") = 0 Then
    CurrentDb.Execute c_SQL0, dbFailOnError
    Else
    CurrentDb.Execute "DELETE FROM Tbl_FileNames;", dbFailOnError
    End If
    strFileName = Dir(c_Path & "*.pdf")
    Do While Len(strFileName) > 0
    strSQL = "INSERT INTO Tbl_FileNames ( FileName ) VALUES ( '" & strFileName & "');"
    CurrentDb.Execute strSQL, dbFailOnError
    strFileName = Dir
    Loop
    strSQL = "INSERT INTO Tbl_Links ( FileName, FileLink ) " & _
    "SELECT a.FileName, '" & c_Path & "' & a.FileName " & _
    "FROM ( SELECT Tbl_FileNames.FileName " & _
    "FROM Tbl_Links RIGHT JOIN Tbl_FileNames ON Tbl_Links.FileName = Tbl_FileNames.FileName " & _
    "WHERE Tbl_Links.FileName Is Null " & _
    ") AS a;"
    CurrentDb.Execute strSQL, dbFailOnError

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •