Message Box If File not found and stop the script

[jdawg_1989]

I currently have the following VBA Code linked to a button on a form.

Private Sub UpdateDBbutton_Click()

DoCmd.Echo False, "Importing latest data to DB, please be patient..."
DoCmd.Hourglass True 'turn on the hour glass

    ' The following Code Deletes all records from Port Usage Table

DoCmd.SetWarnings (False)
Dim strquery As String
strquery = "DELETE [Port Usage].Locaity, [Port Usage].location, [Port Usage].Id, [Port Usage]
FROM [Port Usage];"
DoCmd.RunSQL strquery
DoCmd.SetWarnings (True)

    ' The following Code imports CSV file to Port Usage Table
DoCmd.TransferText acImportDelim, , "Port Usage", _
     "\\sever\folder\Port Usage.csv", -1
     
    ' The following Code updates the Last Update Date Table with the latest time and date
DoCmd.SetWarnings (False)
Dim strquery2 As String
strquery2 = "UPDATE [Last Update Date] SET [Last Update Date].[Last Update Date] = Now();"
DoCmd.RunSQL strquery2
DoCmd.SetWarnings (True)

DoCmd.Echo True, ""
DoCmd.Hourglass False 'turn off the hour glass

MsgBox "Database updated successfully! :)"
End Sub

The Code basically deletes contents of a table already in the database and imports a .CSV file to the table it's wiped. The code then goes on to update another table with todays date which I link to, to say when the DB was last updated. A message box then pops up to tell me the DB was updated successfully.

The .CSV file is located on a server which not all users have access to, so I'd like a popup message something like "Connection to Server failed, would you like to retry?" If Yes I want it to retry and If No I want it to stop running the remainder of the code.

I've been playing around for hourUs with no results. Any ideas are welcome, thanks in advance.

 

[ChrisO]

>>If Yes I want it to retry and If No I want it not quit running the remainder of the code.<<
That sentence seems a little odd so could you please verify it.

If they select No Retry the code continues to run; is that correct?

Chris.

 

[jdawg_1989]

>>If Yes I want it to retry and If No I want it not quit running the remainder of the code.<<
That sentence seems a little odd so could you please verify it.

If they select No Retry the code continues to run; is that correct?

Chris.

Appologies, bad writing on my part, to clarify:
If they select not to retry and want the code to stop.

 

[ChrisO]

Well the logic looks like it would do the delete before the import was attempted and failed.

I think it would probably be better to test if the CSV is available before doing anything else.
If the CSV is available then do it all, if not then let them know the situation and do nothing else, not even a retry.
If they want to retry they can push the button again.

Chris.

 

[jdawg_1989]

I agree, sounds a better way of doing things.

I'm a complete newb at this so my logic isn't the best!

How would I amend my code to do a check for the csv at the beginning?

 

[ChrisO]

This is not totally tested but should be close: -

Private Sub UpdateDBbutton_Click()
    Dim intFileNumber As Integer
    
    Const conDataFile As String = "\\sever\folder\Port Usage.csv"
    
    intFileNumber = FreeFile()

    On Error Resume Next
    Open conDataFile For Input As #intFileNumber
    
    If (Err.Number <> 0) Then
        Close intFileNumber
        On Error GoTo 0
        
        MsgBox conDataFile & "  is not available at this time." & vbNewLine & _
               "Please try again later."
     Else
        Close intFileNumber
        On Error GoTo 0
        
        CurrentDb.Execute "DELETE * FROM [Port Usage];", 128
        
        DoCmd.TransferText acImportDelim, , "Port Usage", conDataFile, -1
        
        CurrentDb.Execute "UPDATE [Last Update Date] SET [Last Update Date].[Last Update Date] = Now();", 128
        
        MsgBox "Database updated successfully! :)"
    End If

End Sub

Chris.

 

[jdawg_1989]

ChrisO,

I put the egg timer code back in and it works brilliantly!

Many Thanks.