Excel Import and Replace Data in Table (1 Viewer)

Private Sub cmdImport_Click()

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Is the updated file Task Import placed in 'Z:\PM Metrics\Task Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbltask
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete [tblTask].* from [tblTask]"
   DoCmd.SetWarnings True

'Import new records from Excel file into tblTask
   DoCmd.TransferSpreadsheet acImport, 8, "tblTask", "Z:\PM Metrics\Task Import.xls", True, ""
Else
DoCmd.OpenForm "frmmenu"
End If

End Sub

'-----------------------------------------------------------------------------
' Utilities from http://blog.nkadesign.com/microsoft-access/
' (c) Renaud Bompuis, 2008-2009
' Licensed under the Creative Commons Attribution License
' http://creativecommons.org/licenses/by/3.0/
' http://creativecommons.org/licenses/by/3.0/legalcode
'
' Free for re-use in any application or tutorial providing clear credit
' is made about the origin of the code and a link to the site above
' is prominently displayed where end-user can access it.
'-----------------------------------------------------------------------------

Option Compare Database
Option Explicit

' Time out set to 60 iterations, after which the batch file should delete itself
Private Const TIMEOUT = 60

'-----------------------------------------------------------------------------
' Restart the current application - v1.2 09AUG2008
' How does it work:
' First, we create a small batch file to which we pass the path and extension
' of the Access executable, the current database and its lock file extension.
' Then we run that script and close the application.
' The script monitors the presence of the lock file.
' As soon as Access removes the lock file:
' - if the Compact option was given, then we compact the database first
' - we open the database again.
' The script will delete itself automatically.
'
' For the batch file, some references:
' http://www.dx21.com/HOME/ARTICLES/P2P/ARTICLE.ASP?CID=12
' http://malektips.com/dos0017.html
' http://www.catch22.net/tuts/selfdel.asp
'-----------------------------------------------------------------------------
Public Sub Restart(Optional Compact As Boolean = False)
    Dim scriptpath As String
    
    ' Construct the full path of our temporary batch file
    scriptpath = Application.CurrentProject.FullName & ".dbrestart.bat"
    
    ' if the script already exists, then check it isn't an old remnant
    ' that has passed it's timeout.
    If Dir(scriptpath, vbNormal) <> "" Then
        If DateAdd("s", TIMEOUT * 2, FileDateTime(scriptpath)) < Date Then
            ' We've passed twice the batch file timeout, giving ample time for
            ' it to exectute, so if it's still there, it's most probably a dud
            Kill scriptpath
        Else
            ' Timeout hasn't expired beyond the acceptable limit, so it's probably
            ' still active, just try to close the application again
            Application.Quit acQuitSaveAll
            Exit Sub
        End If
    End If
    
    ' Construct the batch file
    ' Note that the TIMEOUT value is only used as a loop counter and
    ' we do not really count elapsed time in the batch file.
    ' The ping command takes some time to load and start and even though
    ' we set its timeout to 100ms, it will take much longer than that to
    ' execute.
    ' If we've been asked to comnpact the database, we launch the database
    ' using the /compact command line switch
    Dim s As String
    s = s & "SETLOCAL ENABLEDELAYEDEXPANSION" & vbCrLf
    s = s & "SET /a counter=0" & vbCrLf
    s = s & ":CHECKLOCKFILE" & vbCrLf
    s = s & "ping 0.0.0.255 -n 1 -w 100 > nul" & vbCrLf
    s = s & "SET /a counter+=1" & vbCrLf
    s = s & "IF ""!counter!""==""" & TIMEOUT & """ GOTO CLEANUP" & vbCrLf
    s = s & "IF EXIST ""%~f2.%4"" GOTO CHECKLOCKFILE" & vbCrLf
    If Compact Then
        s = s & """%~f1"" ""%~f2.%3"" /compact" & vbCrLf
    End If
    s = s & "start "" "" ""%~f2.%3""" & vbCrLf
    s = s & ":CLEANUP" & vbCrLf
    s = s & "del %0"
    
    ' Write batch file
    Dim intFile As Integer
    intFile = FreeFile()
    Open scriptpath For Output As #intFile
    Print #intFile, s
    Close #intFile
    
        
    ' Create the arguments to be passed to the script
    ' Here we pass it the full path to the database minus the extension which we pass separately
    ' this is done so that we can reconstruct the path to the lock file easily in the script.
    ' The extension to the lock file is also passed as a third argument.
    Dim dbname As String, ext As String, lockext As String, accesspath As String
    Dim idx As Integer
    
    ' Get the path to the msaccess executable, wherever that is
    accesspath = SysCmd(acSysCmdAccessDir) & "msaccess.exe"
    
    ' Find the extension, starting from the end
    For idx = Len(CurrentProject.FullName) To 1 Step -1
        If Mid(CurrentProject.FullName, idx, 1) = "." Then Exit For
    Next idx
    dbname = Left(CurrentProject.FullName, idx - 1)
    ext = Mid(CurrentProject.FullName, idx + 1)
    
    ' Depending on the database extension, determine its lock file extension
    If Left(ext, 2) = "ac" Then
        lockext = "laccdb"
    Else
        lockext = "ldb"
    End If
    
    ' Call the batch file
    s = """" & scriptpath & """ """ & accesspath & """ """ & dbname & """ " & ext & " " & lockext
    Shell s, vbHide
    
    ' Close our application
   Application.Quit acQuitSaveAll
End Sub

Utilities.Restart Compact:=Nz(ckCompact, False)

Private Sub cmdImport1_Click()

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Is the updated file 'Task Import.xls' placed in 'Z:\PM Metrics\Task Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbltask
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblTask].* from [tblTask]"
DoCmd.SetWarnings True

msg = "Is the updated file 'Project Import.xls' placed in 'Z:\PM Metrics\Project Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tblproject
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblProject].* from [tblProject]"
DoCmd.SetWarnings True

msg = "Is the updated file 'Data Import.xls' placed in 'Z:\PM Metrics\Data Import.xls'?"
button = vbYesNo + vbDefaultButton2
title = "File Location Checkpoint"

response = MsgBox(msg, button, title)
If response = vbYes Then
'Delete old records from tbldata
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [tblData].* from [tblData]"
DoCmd.SetWarnings True

Application.SetOption ("Auto Compact"), 1
Application.SetOption "Show Status Bar", True
'Utilities.Restart Compact:=Nz(ckCompact, False)
vStatusBar = SysCmd(acSysCmdSetStatus, "COMPACT DATABASE!")
DoCmd.Quit

Else
DoCmd.OpenForm "frmmenu"
End If
End If
End If
End Sub

Private Sub cmdImport2_Click()
   DoCmd.TransferSpreadsheet acImport, 8, "tblTask", "Z:\PM Metrics\Task Import.xls", True, ""
   DoCmd.TransferSpreadsheet acImport, 8, "tblProject", "Z:\PM Metrics\Project Import.xls", True, ""
   DoCmd.TransferSpreadsheet acImport, 8, "tblData", "Z:\PM Metrics\Data Import.xls", True, ""

Dim msg As String, button As Variant, title As String, response As Variant
msg = "Import Complete"
button = vbOK
title = "Import Successful"

response = MsgBox(msg, button, title)
If response = vbOK Then
Else
End If

End Sub