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

    Checking files exist (MSACCESS2003)

    I am trying to run through tables checking the data held on directories, and directories exist on a drive. However if I randomly remove a directory on the drive or a file from a directory, I get plagued with messages at "Do While Not rst.EOF" where it halts. It all seems logical but I cannot figure out why it goes wrong?

    Public Sub CHECKTABLES()
    ' Check documents exist on drive
    Dim ty As Integer
    Dim strFullName As String
    Dim Tname As String

    For ty = 1 To 6

    Select Case ty

    Case 1
    ' Main directorys
    MySql = "SELECT tblDirectory.SD1DOC FROM tblDirectory;"
    Tname = "tblDirectory"
    Case 2
    ' Main directory sub directories
    MySql = "SELECT tblSubDir.SD1DOC FROM tblSubDir;"
    Tname = "tblSubDir"
    Case 3
    'Main directory documents
    MySql = "SELECT MainDirDocs.SD1DOC FROM MainDirDocs;"
    Tname = "MainDirDocs"
    Case 4
    'Sub directories of MAIN DIRECTORIES
    MySql = "SELECT tblSubDirectorySubDir.SD1DOC FROM tblSubDirectorySubDir;"
    Tname = "tblSubDirectorySubDir"
    Case 5
    ' Main directory sub directory documents
    MySql = "SELECT SubDirectoryDocs.SD1DOC FROM SubDirectoryDocs;"
    Tname = "SubDirectoryDocs"
    Case 6
    'Sub directories of subdirectories documents
    MySql = "SELECT SubDirectorySubDocs.SD1DOC FROM SubDirectorySubDocs;"
    Tname = "SubDirectorySubDocs"

    End Select

    Set rst = db.OpenRecordset(MySql, dbOpenDynaset)

    DoCmd.SetWarnings False

    Do While Not rst.EOF

    strFullName = rst("SD1DOC")

    'Test if directory or file exists
    If FileOrDirExists(strFullName) Then
    ' No action
    Else
    ' Not there so delete in table
    MsgBox "MISSING" & " " & strFullName
    strDeleteSQL = "DELETE FROM [" & Tname & "] WHERE SD1DOC = '" & rst("SD1DOC") & "' "
    DoCmd.RunSQL (strDeleteSQL)
    DoCmd.SetWarnings True

    End If

    rst.MoveNext
    Loop
    Set rst = Nothing

    Next ty

    End Sub

    Function FileOrDirExists(PathName As String) As Boolean

    Dim iTemp As Integer

    'Ignore errors to allow for error evaluation
    On Error Resume Next
    iTemp = GetAttr(PathName)

    'Check if error exists and set response appropriately
    Select Case Err.Number
    Case Is = 0
    FileOrDirExists = True
    Case Else
    FileOrDirExists = False
    End Select

    'Resume error checking
    On Error GoTo 0
    End Function

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

    Re: Checking files exist (MSACCESS2003)

    What does the error message say? What is the error number?

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

    Re: Checking files exist (MSACCESS2003)

    Thanks Hans, runtime eror 3167

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

    Re: Checking files exist (MSACCESS2003)

    Try replacing the lines

    strDeleteSQL = "DELETE FROM [" & Tname & "] WHERE SD1DOC = '" & rst("SD1DOC") & "' "
    DoCmd.RunSQL (strDeleteSQL)

    with

    rst.Delete

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

    Re: Checking files exist (MSACCESS2003)

    Wonderful Hans, that fixed it. many thanks.

Posting Permissions

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