Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with recordset (MSACCESS 2003)

    I am trying to loop documents held in a folder, and then loop a table to see if the records in there, if not then add it. However I have ended up in a hole, as if the destination table is empty the code won't work. Can someone please modify me out of the mess. Many thanks

    Set fs2 = CreateObject("Scripting.FileSystemObject")
    Set fol2 = fs2.getFolder("C:LoggingFootball")

    Set RSA = DB.OpenRecordset("Linktable", dbOpenDynaset)

    ' Get all word documents
    For Each fil2 In fol2.files
    If fil2.Type = "Microsoft Word Document" Then


    Do While Not RSA.EOF
    If RSA![Documentname] <> fil2.Name Then
    ' Put the document name in the table

    Else
    ' Ignore, already in table


    End If
    RSA.MoveNext
    Loop


    End If

    Next

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with recordset (MSACCESS 2003)

    Try this (it's air code, I haven't actually tested it):

    Set fs2 = CreateObject("Scripting.FileSystemObject")
    Set fol2 = fs2.GetFolder("C:LoggingFootball")
    Set RSA = DB.OpenRecordset("Linktable", dbOpenDynaset)
    ' Get all word documents
    For Each fil2 In fol2.files
    If fil2.Type = "Microsoft Word Document" Then
    RSA.MoveFirst
    RSA.FindFirst "DocumentName = " & Chr(34) & fil2.Name & Chr(34)
    If RSA.NoMatch Then
    ' Put the document name in the table
    RSA.AddNew
    RSA!DocumentName = fil2.Name
    RSA.Update
    End If
    End If
    Next fil2

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with recordset (MSACCESS 2003)

    Hi Hans, sorry for the delay. I was in Amsterdam at the RAI exhibitiion centre on Sunday/Monday. It would have been good to have met up to buy you a beer for all your help, but it was all work.

    My problem is if there are no records in the file it's seraching then it does nothing, it passes all the code to put the record in. Best regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with recordset (MSACCESS 2003)

    What happens if you remove the line

    RSA.MoveFirst

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with recordset (MSACCESS 2003)

    Wonderful, that fixed it. Many thanks again

Posting Permissions

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