I have an access database that opens with the windows scheduler and auto opens a form and then the on open action of the form runs a module that imports files and then several queries that process them and a batch script and then finally closes the database.
Howevever, if the files are not there it tries to run the rest and hangs the database.
I need to run an if statement so that if the files are not found then it will just close the database. I can do this pretty easy with an if statement. The problem is that each day the files are delivered and I don't know what the name will be. so I don't now if what is highlighed in red below will work. I have included the function below as well
Code:
Private Sub Command204_Click()
DoCmd.SetWarnings False
'Test to seee if files are there
Const workFolder As String = "E:\OneDrive - TCIX\TracingFiles\*.csv"
If FileExists(workFolder) Then
'Import the csv files
ImportGroup.Import_multiple_csv_files_OneDrive
'Transform the data and add it to the sql server
DoCmd.OpenQuery "q_AppendGroupImportToNSImport_SR", acViewNormal
DoCmd.OpenQuery "q_AppendNSImportToDateTable_SR", acViewNormal
DoCmd.OpenQuery "q_AppendRecordsToBeDeleted-NSImport-Date", acViewNormal
DoCmd.OpenQuery "q_DeleteNoActivityRecords-NSImport-Date", acViewNormal
DoCmd.OpenQuery "q_AppendToPrepTable-NSImport-Date", acViewNormal
DoCmd.OpenQuery "q_UpdateIDforCurrentInitials", acViewNormal
DoCmd.OpenQuery "q_UpdateIDforFormerInitials", acViewNormal
DoCmd.OpenQuery "q_AppendToFinalized-FromPrepTableChangeLink", acViewNormal
'clean up all the data in the tables
DoCmd.OpenQuery "q_DeleteGroupImportData", acViewNormal
DoCmd.OpenQuery "q_DeleteAllRecordsIn-NSImport", acViewNormal
DoCmd.OpenQuery "q_DeleteAllRecordsIn-NSImport-Date", acViewNormal
DoCmd.OpenQuery "q_DeleteAllRecordsIN-PrepTable", acViewNormal
Call RemoveImportErrorTables
Shell "cmd /c ""E:\OneDrive - TCIX\TracingFiles\SaveImportedFilesChangeDir.bat"", vbHide"
DoCmd.SetWarnings True
Application.Quit
Else
Application.Quit
Code:
Public Function FileExists(ByVal path_ As String) As Boolean
FileExists = (Len(Dir(path_)) > 0)
End Function