Results 1 to 2 of 2
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    if statement if no files found

    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

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Check out this link:

    https://social.msdn.microsoft.com/Fo...orum=accessdev

    Cheers,
    Vlad

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Caractors found after sql statement?? Error?
    By d9pierce1 in forum Programming
    Replies: 12
    Last Post: 04-27-2019, 05:25 PM
  2. Replies: 4
    Last Post: 07-21-2017, 01:07 PM
  3. Replies: 7
    Last Post: 05-01-2017, 02:18 PM
  4. Extracting number of files found by module
    By JeroenMioch in forum Access
    Replies: 4
    Last Post: 01-21-2016, 11:43 AM
  5. Replies: 1
    Last Post: 02-21-2011, 09:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums