Either the above problem occurs or Adobe Reader opens but the file isn't displayed. What gives?

- Bob

thanks a lot, I'll try it out. Just busy just like you with all these projects because the other engineer that works with me is gone for vacation for a couple weeks. As soon as I try it I'll post back

Thanks again

I have got a problem probably due to the outdated version of my office. I use access 97 the error I get is Dim cnn As ADODB.Connection says "User-defined type not defined"
So i changed that line to DAO.Database and it stop giving me an error.

The other error is with my Microsoft Scripting Runtime library (I do have the file Scrrun.dll.)
    Dim oFSO As Scripting.FileSystemObject "User-defined type not defined"

Any idea how to fix this issue?

Hi marcin2k,

Its been so long since I used Access 97 (in fact, Microsoft discontinued support for Access 97, effective January 16 2004), that I forgot it doesn't set a reference to ADO. So on the ADO issue you have two choices. While ADO didn't exist when Access 97 came out, you can still use it in an Access 97 app. Just set a reference to the Microsoft ActiveX Data Objects 2.x Library if its on your system. If not install the latest version of MDAC, or use DAO. Here's the DAO version of the code:

Public Sub InsertPDFFiles()
    On Error GoTo ErrorHandler

    ' Data access variables
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCnn As String
    Dim strSQL As String

    ' Microsoft Scripting Runtime library variables (Scrrun.dll.)
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModified As String
    Dim strRowSource As String
    Dim intID As Integer
    Dim strFileHyperLink As String
    Dim blnRecordAdded As Boolean

    Set dbs = OpenDatabase("C:\PDFFiles.mdb")
    Set rst = dbs.OpenRecordset("tblPDFFiles", dbOpenDynaset)


    ' Instantiate scripting objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\Test")
    Set oFiles = oFolder.Files

    ' Get number of days.
    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    intIndex = 0

    ' Insert file names and hyperlink address into table
    For Each oFile In oFiles
        datModified = Format(oFile.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat 7.0 Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                strFileHyperLink = oFile.Name & "#" & oFile.Path & "#"
                Debug.Print strFileHyperLink
            End If

            With rst
                .AddNew
                !FileHyperlink = strFileHyperLink
                .Update
                 blnRecordAdded = True
            End With

        End If
    Next oFile

    rst.Close
    dbs.Close
    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing

    Exit Sub

ErrorHandler:
    rst.Close
    dbs.Close
    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub

On the FileSystemObject issues, while you have a copy of Scrrun.dll on your system, have you set a reference to it in your project (also not set by default). Check your References dialog and add a reference to the Microsoft Scripting Runtime library if you haven't. Let me know how you make out.

Bob

Hello Bob,

I tried a number of things, I edited some of the names in your code as some were invalid and I added the scripting runtime library to my references. That part works but not sure why I get this odd error "DateModified Complex --> Type Mismatch" the file used to be called DateModified Complex but its not anymore i renamed it to test. So I am not sure what its suppose to mean here is the code I got:

Public Sub InsertPDFFiles()
    On Error GoTo ErrorHandler

    ' Data access variables
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCnn As String
    Dim strSQL As String

    ' Microsoft Scripting Runtime library variables (Scrrun.dll.)
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModified As String
    Dim strFileHyperLink As String
    Dim blnRecordAdded As Boolean

    Set dbs = OpenDatabase("c:\a\test.mdb")
    Set rst = dbs.OpenRecordset("tblPDFFiles", dbOpenDynaset)


    ' Instantiate scripting objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\a")
    Set oFile = oFolder.Files

    ' Get number of days.
    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    ' Insert file names and hyperlink address into table
    For Each oFile In oFolder
        datModified = Format(oFile.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                strFileHyperLink = oFile.Name & "#" & oFile.Path & "#"
                Debug.Print strFileHyperLink
            End If

            With rst
                .AddNew
                !FileHyperlink = strFileHyperLink
                .Update
                 blnRecordAdded = True
            End With

        End If
    Next oFile

    rst.Close
    dbs.Close
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing

    Exit Sub

ErrorHandler:
    rst.Close
    dbs.Close
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub


Thanks for the help

I took out the error handler and the error is in the line Set oFile = oFolder.Files "Type Mismatch"

Hi marcin2k,

Sorry for the delay in the reply. You have a small typo in your code.

Set oFile = oFolder.Files

should be

Set oFiles = oFolder.Files

oFiles is plural.

Bob

Hi Bob,

So is oFiles a build in function where I can't use oFile?
Cause I know that the rest of my code uses oFile so I made it oFile when I declared it.

Hi marcin2k,

oFiles is an object variable that stores a reference to the Folder object's Files collection. (oFiles is just an arbitrary variable name I used).

oFile (another arbitrary variable name) is an object variable that holds a reference to a File object in the Files collection.

"For Each oFile In oFolder" iterates through the Folder object's Files collection, examining each File object in turn.

Folder and File are names of objects in the Microsoft Scripting Runtime library.

The oFolder and oFile variables are initialized simply by using the Set statement. They do not need to be declared first using Dim statements.

HTH,

Bob

Sorry I'm struggling,
The code you provided did it work for you? Just curious because I understand what you are doing, I made slight changes that I though would make more sense but not sure why its not working. Can you just take a look at this code and let me know where i am messing up?

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strCnn As String
    Dim strSQL As String

    ' Microsoft Scripting Runtime library variables (Scrrun.dll.)
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModified As String
    Dim strFileHyperLink As String
    Dim blnRecordAdded As Boolean

    Set dbs = OpenDatabase("c:\a\test.mdb")
    Set rst = dbs.OpenRecordset("tblPDFFiles", dbOpenDynaset)


    ' Instantiate scripting objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\a")
    Set oFiles = oFolder.Files

    ' Get number of days.
    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    ' Insert file names and hyperlink address into table
    For Each oFile In oFiles
        datModified = Format(oFiles.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                strFileHyperLink = oFile.Name & "#" & oFile.Path & "#"
                Debug.Print strFileHyperLink
            End If

            With rst
                .AddNew
                !FileHyperlink = strFileHyperLink
                .Update
                 blnRecordAdded = True
            End With

        End If
    Next oFile

    rst.Close
    dbs.Close
    Set oFSO = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing

    Exit Sub

**Error is at line "Set oFiles = oFolder.Files" variable not defined
From what I tried doing is changing the oFiles to oFile since thats what I declared, but from what I understand you need oFile (stores a single file) and oFiles (stores a collection of files). But in the code above oFile is not set and oFiles is not declared. What should I do?

Thanks for all your help so far