Hello all,

I created a couple of macros that are functional, I would just like to have them cleaned up in terms of data types defined, order of setting up variables, etc. A cherry on top would be to have logs insert into a different log table that state "N number of records were inserted for the date of on this date". If the import fails, then I would like to insert the error code of "Failed to insert for this date on this date" on the same table. As you will note, in one of my macros it prompts me for a date value so "for the date" and "on this date" can be different.



Code:
Public Sub import_query()
Dim yr As Integer, Months As Integer, Days01 As Integer, SQLSyntax As String, BeforeInsert As Variant, AfterInsert As Variant, t As TableDef
yr = Year(DateAdd("d", -1, (Date)))
Months = Month(DateAdd("d", -1, (Date)))
Days01 = Day(DateAdd("d", -1, (Date)))
BeforeInsert = DMax("id", "TelecomDailyReport")
BeforeInsert2 = BeforeInsert + 1

If Months < 10 Then
    Dim mo As String
    mo = "0" & Months
    Else
    mo = Months
    End If

If Days01 < 10 Then
    Dim Days As String
    Days = "0" & Days01
    Else
    Days = Days01
    End If


    DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="ImportTelecomDailyReport", TableName:="TelecomDailyReport", FileName:="\\sknxfile03.nova.prv\departments\HelpDesk\Reporting\Daily_Reports\IVR_PWD_RESET_" & yr & mo & Days & ".csv", hasfieldnames:=True
    AfterInsert = DMax("id", "TelecomDailyReport")
    SQLSyntax = "UPDATE TelecomDailyReport SET TelecomDailyReport.Dates = " & Format(DateAdd("d", -1, (Date)), "\#yyyy-m-d\#") & " where TelecomDailyReport.ID between (SELECT [TelecomDailyReport.ID] FROM TelecomDailyReport where [TelecomDailyReport.ID] = " & BeforeInsert2 & ") and (SELECT [TelecomDailyReport.ID] FROM TelecomDailyReport where [TelecomDailyReport.ID] = " & AfterInsert & ");"
    DoCmd.RunSQL SQLSyntax
    
    For Each t In CurrentDb.TableDefs
        If t.Name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
    Next
    
End Sub
and the other Macro is:

Code:
Public Sub import_query()
Dim myValue As Variant, Year As String, Days As String, Months As String, StringDate As String, DateDate As Date, BeforeInsert As Integer, AfterInsert As Integer, SQLSyntax As String, BeforeInsert2 As Integer

BeforeInsert = DMax("id", "TelecomDailyReport")
BeforeInsert2 = BeforeInsert + 1
myValue = InputBox("Put in the exact numeric sequence in the excel file you are trying to import.  An example is 20160310")
    DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="ImportTelecomDailyReport", TableName:="TelecomDailyReport", FileName:="\\sknxfile03.nova.prv\departments\HelpDesk\Reporting\Daily_Reports\IVR_PWD_RESET_" & myValue & ".csv", hasfieldnames:=True
AfterInsert = DMax("id", "TelecomDailyReport")
AfterInsert2 = AfterInsert - 1


Year = Left(myValue, 4)
Days = Right(myValue, 2)
Months = Mid(myValue, 5, 2)

StringDate = (Year & "/" & Months & "/" & Days)
DateDate = StringDate
MsgBox (BeforeInsert - AfterInsert)

SQLSyntax = "UPDATE TelecomDailyReport SET TelecomDailyReport.Dates = " & Format(DateDate, "\#yyyy-m-d\#") & " where TelecomDailyReport.ID between (SELECT [TelecomDailyReport.ID] FROM TelecomDailyReport where [TelecomDailyReport.ID] = " & AfterInsert & ") and (SELECT [TelecomDailyReport.ID] FROM TelecomDailyReport where [TelecomDailyReport.ID] = " & BeforeInsert2 & ");"
    DoCmd.RunSQL SQLSyntax

For Each t In CurrentDb.TableDefs
    If t.Name Like "*ImportErrors*" Then DoCmd.RunSQL ("DROP TABLE " & t.Name)
Next

End Sub