Automate import of daily report from another source

[accessbro]

The multi-user application I'm making uses data from another system. I get reports in csv format e-mailed to me in zip format. Since it's a multi-user system, I have to create a table with the records from the csv I get an error message that someone is viewing the data if I try viewing it while someone is viewing it.

When I get the e-mail with the latest version of the csv, I unzip it and replace the older file in the folder location that the Access database is linked to. I have a sub routine that deletes everything from the table and inserts the records from the latest csv. I execute the sub by pressing a button.

Is there a way to automate unzipping the file, extracting it to the folder and running the sql code in VBA?

[orange]

By receiving data from a report, you are getting processed and formatted data. It might be better if you were to request data in a format suited to your needs. You didn't say whether this is all within your company, or whether it is something from some exterior source.
Getting data from as close as possible to the source in a format you can use directly may be an approach worth pursuing.

Good luck.

[ranman256]

I use this code...works great.

usage:
UnZip vFile, sTargetDir

Public Sub UnZip( _
    ZipFile As String, _
    Optional TargetFolderPath As String = vbNullString, _
    Optional OverwriteFile As Boolean = False)



On Error GoTo ErrHandler
    Dim oApp As Object
    Dim FSO As Object
    Dim fil As Object, fMaxFile As Object
    Dim DefPath As String
    Dim strDate As String

    Set FSO = CreateObject("Scripting.FileSystemObject")
    If Len(TargetFolderPath) = 0 Then
        DefPath = Application.Path & ""
    Else
        If FSO.folderexists(TargetFolderPath) Then
            DefPath = TargetFolderPath & ""
        Else
            Err.Raise 53, , "Folder not found"
        End If
    End If

    If FSO.FileExists(ZipFile) = False Then
        MsgBox "System could not find " & ZipFile _
            & " upgrade cancelled.", _
            vbInformation, "Error Unziping File"
        Exit Sub
    Else
        'Extract the files into the newly created folder
        Set oApp = CreateObject("Shell.Application")

        With oApp.NameSpace(ZipFile & "")
            If OverwriteFile Then
                For Each fil In .Items
                    'If fMaxFile Is Nothing Then Set fMaxFile = fil
                    'If fil.Size > fMaxFile.Size Then Set fMaxFile = fil        'FIND THE LARGEST FILE
                    
                    If FSO.FileExists(DefPath & fil.Name) Then
                        Kill DefPath & fil.Name
                    End If
                Next
            End If
            oApp.NameSpace(CVar(DefPath)).CopyHere .Items
            
            'Set UnZip = fMaxFile
        End With

        On Error Resume Next
        Kill Environ("Temp") & "Temporary Directory*"

            'clear mem
        Set oApp = Nothing
        Set FSO = Nothing
        Set fil = Nothing
        'Set fMaxFile = Nothing
    End If

ExitProc:
    On Error Resume Next
    Set oApp = Nothing
    Exit Sub
ErrHandler:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Unexpected error"
            
    End Select
    Resume ExitProc
    Resume
End Sub