Procedure does not work in Access 2007

[JudyJones]

I have the following code in a procedure to check that someone does not have the backend of the database open before updating several of the tables:

Code: Select all

Private Sub cmdWUpdate_Click()

    Dim i As Integer
    Dim fs As Variant

    Set fs = Application.FileSearch
    With fs
        .NewSearch
        .LookIn = "N:\sasixp\attendance\"
        .filename = "OAR_ao.ldb"
        .Searchsubfolders = True
    If .Execute() > 0 Then
        'MsgBox "There were " & .Foundfiles.Count & " file(s) found."
        MsgBox "You must wait until the attendance officer has closed the OAR program before updating data"
        Exit Sub
    
        For i = 1 To .Foundfiles.Count
        'MsgBox .Foundfiles(i)
        Next i
    Else
        'MsgBox "There were no files found."
    
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelete_tblAPRN"
    DoCmd.OpenQuery "qryAppend_tblAPRN"
    DoCmd.OpenQuery "qryDelete_tblASTU"
    DoCmd.OpenQuery "qryAppend_tblASTU"
    DoCmd.OpenQuery "qryDelete_tblAATG"
    DoCmd.OpenQuery "qryAppend_tblAATG"
    DoCmd.OpenQuery "qryDeleteTblDaysEnrolled"
    DoCmd.OpenQuery "qryAppendTblDaysEnrolled"
    DoCmd.OpenQuery "qryDeleteTblMembership"
    DoCmd.OpenQuery "qryAppendTblMembership"
    DoCmd.OpenQuery "qryUpdateEnrolledMembership"
    DoCmd.OpenQuery "qryDeletetblAdmAsn"
    DoCmd.OpenQuery "qryAppendLastName2AdmAsn"
    DoCmd.OpenQuery "qryUpdateAdm1"
    DoCmd.OpenQuery "qryUpdateAdm2"
    DoCmd.OpenQuery "qryUpdateAdm3"
    DoCmd.OpenQuery "qryUpdateAdm4"
  
    Me.WUpdate_Date = Date
    Me.DUpdate_Date = Date
    DoCmd.SetWarnings True
    Me.Requery
    End If
    End With
End Sub

In Access 2007 I get the following error message: "You entered an expressiuon that has an invalid reference to the property FileSearch"

I can comment out the FileSearch code and it works fine. Is there a change I can make to this code to get it to work in Access 2007?

[HansV]

In its infinite wisdom, Microsoft has removed the FileSearch object from Office 2007 VBA.

You could use code like this:

Code: Select all

Private Sub cmdWUpdate_Click()
    Dim strFile As String
    strFile = Dir("N:\sasixp\attendance\OAR_ao.ldb")
    If Not strFile = "" Then
        MsgBox "You must wait until the attendance officer has closed the OAR program before updating data"
        Exit Sub
    End If

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDelete_tblAPRN"
    DoCmd.OpenQuery "qryAppend_tblAPRN"
    DoCmd.OpenQuery "qryDelete_tblASTU"
    DoCmd.OpenQuery "qryAppend_tblASTU"
    DoCmd.OpenQuery "qryDelete_tblAATG"
    DoCmd.OpenQuery "qryAppend_tblAATG"
    DoCmd.OpenQuery "qryDeleteTblDaysEnrolled"
    DoCmd.OpenQuery "qryAppendTblDaysEnrolled"
    DoCmd.OpenQuery "qryDeleteTblMembership"
    DoCmd.OpenQuery "qryAppendTblMembership"
    DoCmd.OpenQuery "qryUpdateEnrolledMembership"
    DoCmd.OpenQuery "qryDeletetblAdmAsn"
    DoCmd.OpenQuery "qryAppendLastName2AdmAsn"
    DoCmd.OpenQuery "qryUpdateAdm1"
    DoCmd.OpenQuery "qryUpdateAdm2"
    DoCmd.OpenQuery "qryUpdateAdm3"
    DoCmd.OpenQuery "qryUpdateAdm4"

    Me.WUpdate_Date = Date
    Me.DUpdate_Date = Date
    DoCmd.SetWarnings True
    Me.Requery
End Sub

[JudyJones]

Your assistance was invaluable. The revised code works just fine in version 2007 as well as older versions some of our users are still using.