Add IF statement to converted macro

[Graham T]

I have created a simple database to import information into a single table, from databases that are sent into us from students.

To begin with I had created a macro to do this option, but now see the need to include better error handling, as if mistakes are made, the admin people who will use this may have problems.

Basically, they will copy the Job_Search.mdb to the drive where the master database is held.

What I would like to include is the IF statement to check that this database (Job_Search.mdb) is present before the import starts, and if not to exit the routine.

Below is what I have from converting the macro, and would appreiciate help in including this statement as I am unsure as how to code the part that looks to see if the database has actually been copied to the correct drive:

'------------------------------------------------------------
' mcrImport_Append_Delete
'
'------------------------------------------------------------
Function mcrImport_Append_Delete()
On Error GoTo mcrImport_Append_Delete_Err

    DoCmd.Echo False, ""
    DoCmd.TransferDatabase acImport, "Microsoft Access", "\\Dilbert\Data\Recruitment\Students\2002_Master_Employer_Database\Job_Search.mdb", acTable, "Employers", "Employers", False
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_Append_Student_To_Master", acNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.DeleteObject acTable, "Employers1"
    Call Delete_Database
    Beep
    MsgBox "Data Import Complete.  Student Database has been deleted from the drive.", vbInformation, "Master Employer Database"


mcrImport_Append_Delete_Exit:
    Exit Function

mcrImport_Append_Delete_Err:
    MsgBox Error$
    Resume mcrImport_Append_Delete_Exit

End Function

TIA

Graham

 

[cpod]

Try this:

With Application.FileSearch
.LookIn = "\\Dilbert\Data\Recruitment\Students\2002_Master_Employer_Database"
.Filename = "Job_Search.mdb"
.MatchTextExactly = True
.FileType = msoFileTypeDatabases
.Execute
If .FoundFiles.Count = 0 Then
MsgBox "Database not found"
Exit Function
End If
End With

 

[Graham T]

Thanks for that cpod

However, when I try to run this I get the following message:

"Invalid procedure call or argument"

It seems to be pointing towards the line:

.FileType = msoFileTypeDatabases

Any ideas?

Graham

[This message has been edited by Graham T (edited 04-11-2002).]

[This message has been edited by Graham T (edited 04-11-2002).]

 

[Graham T]

I have attempted a different approach using the following:

Public Sub mcrImport_Append_Delete()
On Error GoTo mcrImport_Append_Delete_Err

Set fs = CreateObject("Scripting.FileSystemObject")
If Not fs.FileExists("\\Dilbert\Data\Recruitment\Students\2002_Master_Employer_Database\Job_Search.mdb") Then
    MsgBox "The Job_Search database has not been copied to the folder.  Please copy and run the Import."
    Exit Sub
End If


    DoCmd.Echo False, ""
    DoCmd.TransferDatabase acImport, "Microsoft Access", "\\Dilbert\Data\Recruitment\Students\2002_Master_Employer_Database\Job_Search.mdb", acTable, "Employers", "Employers", False
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qry_Append_Student_To_Master", acNormal, acEdit
    DoCmd.SetWarnings True
    DoCmd.DeleteObject acTable, "Employers1"
    Call Delete_Database
    Beep
    MsgBox "Data Import Complete.  Student Database has been deleted from the drive.", vbInformation, "Master Employer Database"

Set fs = Nothing
    
mcrImport_Append_Delete_Exit:
    Exit Sub

mcrImport_Append_Delete_Err:
    MsgBox Error$
    Resume mcrImport_Append_Delete_Exit

End Sub

This works to a point, tells me if the database is present or not and exits the sub if not.

If the database is present, it does the import but then just locks the database up and I have to close down.

It appears to be stuck in some kind of loop, but I am unsure as to how to stop this.

Any ideas or advice welcome

Graham