Storing and Retrieving 'DateLastStored'

[MJ]

We have built a database that imports data from a number of files that are
produced daily and compiles it into a single "MASTER" table. Most of the
time the user will only be importing a single day at a time, but after
weekends and when we initiate this database we will process multiple dates at
a time.

This importing process works, but we want to put a safety into the code to
prevent loading of previously loaded dates.

My initial thought was a simple If then-Else where:

========
Private Sub CmdImport_Click() 'Import Files -- using the dates input on
the form

On Error GoTo Err_CmdImport_Click

Dim dbMyDB As Database
Dim StartDate, EndDate, PrevDate As Date
Dim strStart, strStart2, DateComplete, strEnd As String
Dim d, DaystoImport As Integer

Set dbMyDB = CurrentDb
DoCmd.SetWarnings False

PrevDate = SafetyCheck.LastRunDate ' Added to check for date of the
last time
' this
import function was run, to
' prevent
overwriting exisiting data
' with older
data.
StartDate = Me.StartDate
EndDate = Me.EndDate

If (PrevDate <= StartDate) then
MsgBox "Re-Enter your 'Start Date', it must be AFTER " & _
DateLastStored, vbCritical
...

Else
DaystoImport = (EndDate - StartDate)
For d = 0 To DaystoImport ' Added DateAdd function to handle
calendar
' issues (EOM) and
changed when the date is
' incremented (prior
to converting date to
' string variable)
for input file naming.

DateComplete = Format(DateAdd("d", d, StartDate), "mm/dd/yyyy")
strStart = Format(DateAdd("d", d, StartDate), "yymmdd")
strStart2 = Format(DateAdd("d", d, StartDate), "mmdd")

Import files loop...

Next d

SafetyCheck.LastRunDate = EndDate
' Added to store the date of the last
files
' imported.
MsgBox vbNewLine & vbNewLine & "File(s) Importing Completed" & _
vbNewLine & vbNewLine, vbOKOnly
DoCmd.SetWarnings True

End If

Exit_CmdImport_Click:
Exit Sub

Err_CmdImport_Click:
MsgBox Err.Description
Resume Exit_CmdImport_Click

End Sub
=========

Issues:
(1) Storing the "End Date" into a table with a single record and entry.
(2) Retrieving the DateLastStored from that table to make the
comparison.

*
* Does anyone have any inputs on how to make this idea work?
*

Or... my second thought was to somehow link the DateLastStored (mentioned
above) to the UserInterface form so that the User only needs to enter the end
date of their importing. I would need to tweak the way the dates are handled
within the import loop, but I would have similar issues as above plus...

Issues (continued):
(3) Linking the date stored in the table with a single record and
entry (DateLastStored) to the "Start Date" on the UserInterface form.

*
* Does anyone have any inputs on how to make either of these ideas work?
*

I sincerely hope that I have included enough information to resolve my
problems. Thank you in advance for your assistance and inputs.

MJ

 

[Steve Schapel]

MJ,

Sorry. just a quick tangential comment at this stage, as I haven't quite
got a handle yet on the real questions you are asking. But I thought I
should point out that your variable declarations are probably not doing
what you want them to. For example, this line:
Dim StartDate, EndDate, PrevDate As Date
.... I imagine you think is creating 3 date variables, whereas it is not,
the only date variable will be the PrevDate. You should do it like this:
Dim StartDate As Date, EndDate As Date, PrevDate As Date
.... or, in my opinion, better to do each one on a separate line:
Dim StartDate As Date
Dim EndDate As Date
Dim PrevDate As Date

 

[Bob Quintal]

You only need a single row in a one column table. After running an
import session, Just trigger an update query that sets now() to the
field.

Tyhe next time you open the session, just retrieve this date/time
value using DLookup() and advise the user to "Please Only import
Files created after " & NameOfField & "!"

You could even have code check the fully-qualified file creation
date and pop up a warning if it's before your date/time stamp.

Q

We have built a database that imports data from a number of files
that are produced daily and compiles it into a single "MASTER"
table. Most of the time the user will only be importing a single
day at a time, but after weekends and when we initiate this
database we will process multiple dates at a time.

This importing process works, but we want to put a safety into the
code to prevent loading of previously loaded dates.

My initial thought was a simple If then-Else where:

========
Private Sub CmdImport_Click() 'Import Files -- using the dates
input on the form

On Error GoTo Err_CmdImport_Click

Dim dbMyDB As Database
Dim StartDate, EndDate, PrevDate As Date
Dim strStart, strStart2, DateComplete, strEnd As String
Dim d, DaystoImport As Integer

Set dbMyDB = CurrentDb
DoCmd.SetWarnings False

PrevDate = SafetyCheck.LastRunDate ' Added to check for date
of the
last time
'
this
import function was run, to
'
prevent
overwriting exisiting data
'
with older
data.
StartDate = Me.StartDate
EndDate = Me.EndDate

If (PrevDate <= StartDate) then
MsgBox "Re-Enter your 'Start Date', it must be AFTER " &
_
DateLastStored, vbCritical
...

Else
DaystoImport = (EndDate - StartDate)
For d = 0 To DaystoImport ' Added DateAdd function to
handle
calendar
' issues
(EOM) and
changed when the date is
'
incremented (prior
to converting date to
' string
variable)
for input file naming.

DateComplete = Format(DateAdd("d", d, StartDate),
"mm/dd/yyyy") strStart = Format(DateAdd("d", d,
StartDate), "yymmdd") strStart2 = Format(DateAdd("d",
d, StartDate), "mmdd")

Import files loop...

Next d

SafetyCheck.LastRunDate = EndDate
' Added to store the date
of the last
files
' imported.
MsgBox vbNewLine & vbNewLine & "File(s) Importing
Completed" & _
vbNewLine & vbNewLine, vbOKOnly
DoCmd.SetWarnings True

End If

Exit_CmdImport_Click:
Exit Sub

Err_CmdImport_Click:
MsgBox Err.Description
Resume Exit_CmdImport_Click

End Sub
=========

Issues:
(1) Storing the "End Date" into a table with a single record
and entry. (2) Retrieving the DateLastStored from that table
to make the
comparison.

*
* Does anyone have any inputs on how to make this idea work?
*

Or... my second thought was to somehow link the DateLastStored
(mentioned above) to the UserInterface form so that the User only
needs to enter the end date of their importing. I would need to
tweak the way the dates are handled within the import loop, but I
would have similar issues as above plus...

Issues (continued):
(3) Linking the date stored in the table with a single
record and
entry (DateLastStored) to the "Start Date" on the UserInterface
form.

*
* Does anyone have any inputs on how to make either of these ideas
work? *

I sincerely hope that I have included enough information to
resolve my problems. Thank you in advance for your assistance and
inputs.

MJ

 

[MJ]

Steve,

Thank you for pointing that out, I can fix that immediately. It has been a
while since I have done any amount of coding, but am picking it back up
quickly and having this resource is amazing.

MJ

 

[MJ]

Bob,

I think we are on the right track, but not quite there.

Your suggestion says to use an update query that sets now() as the value of
the field. If I was looking for the actual date of the update that would
work, but what I am looking to populate that field with is the date of the
latest files imported into the database (the EndDate value). Would it be
possible to do the same update query, but set the field to the value of
EndDate?

Thanks for the quick reply.

MJ

 

[Pat Hartman]

I would create a table that holds a row for each file imported rather than
tracking just the most recent import. This gives you the most flexibility
assuming that each row that gets imported to the main table also contains
the file date. This allows you to reprocess a file easily. You delete all
the imported rows with the desired date and you delete the date row from the
import tracking table.

 

[Bob Quintal]

Bob,

I think we are on the right track, but not quite there.

Your suggestion says to use an update query that sets now() as the
value of the field. If I was looking for the actual date of the
update that would work, but what I am looking to populate that
field with is the date of the latest files imported into the
database (the EndDate value). Would it be possible to do the same
update query, but set the field to the value of EndDate?

Thanks for the quick reply.

MJ

It was my understanding that one would import all files created
since the last time the session was executed. If this is the case,
now() is still the fencepost against which the user needs to
evaluate whether a file should be imported.

If it isn't the case. then storing the datestamp of the most
recently imported file (available by using the fileDateTime
(pathAnfFileName) function.