Private Sub btnImportForecast_Click()
On Error GoTo ErrHandler
Dim strFileName As String
Dim strTableName As String
Dim strWorksheet As String
strFileName = Dir("Z:\Projects\Kusile Folder\Project Control\CCFS\*.xlsm")
strTableName = "tblImportForecast"
strWorksheet = "Forecast!A:R"
Do Until strFileName = ""
DoCmd.TransferSpreadsheet acImportForecast, acSpreadsheetTypeExcel9, strTableName, _
"Z:\Projects\Kusile Folder\Project Control\CCFS\" & strFileName, True, strWorksheet
strFileName = Dir()
Loop
Dim db As DAO.Database
Dim DeleteBlanksSQL As String
Dim DeleteDuplicatesSQL As String
Dim AppendSQL As String
Dim DeleteImportForecastSQL As String
Dim DeleteBlanks As DAO.QueryDef
Dim DeleteDuplicates As DAO.QueryDef
Dim Append As DAO.QueryDef
Dim DeleteImportForecast As DAO.QueryDef
Set db = CurrentDb()
DeleteBlanksSQL = "DELETE * FROM tblImportForecast WHERE IsNull([Project ID]);"
DeleteDuplicatesSQL = "DELETE tblForecast.fldPrimary FROM tblForecast" & _
" WHERE [fldPrimary] IN (SELECT fldPrimary FROM qryJoinForecastDuplicates);"
AppendSQL = "INSERT INTO tblForecast" & _
" SELECT [Project ID] AS fldID, [Package ID] AS fldPackageID," & _
" [Title of Change] AS fldTitle, [Change Description] AS fldDescription," & _
" [Status] AS fldStatus, [Contract Change Type] AS fldContractType," & _
" [DCF Change Type] AS fldDCFType, [Scope Transfer] AS fldTransfer," & _
" [PCR No] AS fldPCR, [DCF Mod No] AS fldDCFModNo, [Delay Related] AS fldDelay," & _
" [Value] AS fldValue, [Worst] AS fldWorst, [Most] AS fldMost, [Best] AS fldBest," & _
" [Assumption] AS fldAssumption, [Trend No] AS fldTrendNo, [Cont Usage No] AS fldContNo" & _
" FROM tblImportForecast;"
DeleteImportForecastSQL = "DELETE * FROM tblImportForecast;"
Set DeleteBlanks = db.CreateQueryDef("", DeleteBlanksSQL)
DeleteBlanks.Execute
DeleteBlanks.Close
Set DeleteDuplicates = db.CreateQueryDef("", DeleteDuplicatesSQL)
DeleteDuplicates.Execute
DeleteDuplicates.Close
Set Append = db.CreateQueryDef("", AppendSQL)
Append.Execute
Append.Close
Set DeleteImportForecast = db.CreateQueryDef("", DeleteImportForecastSQL)
DeleteImportForecast.Execute
DeleteImportForecast.Close
db.Close
Exit_ErrHandler:
Exit Sub
ErrHandler:
With db
Dim DeleteImportForecastError As DAO.QueryDef
Dim qryCheckTable As DAO.QueryDef
Dim rstCheckTable As DAO.Recordset
Dim DeleteImportForecastErrorSQL As String
Dim CheckTableSQL
DeleteImportForecastErrorSQL = "DELETE * FROM tblImportForecast;"
CheckTableSQL = "SELECT * FROM tblImportForecast;"
Set qryCheckTable = .CreateQueryDef("", CheckTableSQL)
Set rstCheckTable = qryCheckTable.OpenRecordset
If rstCheckTable.RecordCount > 0 Then
Set DeleteImportForecastError = .CreateQueryDef("", DeleteImportForecastErrorSQL)
DeleteImportForecastError.Execute
DeleteImportForecastError.Close
Else
MsgBox (" An Error Occurred." & vbCrLf & _
" Research error than re-ImportForecast." & vbCrLf & _
"Ensure package files are not duplicated.")
End If
.Close
End With
Resume Exit_ErrHandler
End Sub
