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