appending new data

[Anna Warloe]

This might be a dumb question, but I am pretty new to Access....

I am trying to write code in Access to take river forecasts which are
delivered as attachments in emails and append the data into an Access
Database. Some forecasts are updated 4 times a day and others are
only updated daily. I am trying to write a code which will only
update the new forecasts and not cause repetitive data from the
forecasts that only change once a day. (Some sort of "Append new data
only" query). I have looked at some of the posts on here but I don't
understand enough about access to fully understand what they are
suggesting.


Each record that is entered into my master table has the following
fields:

Created Timestamp (when the forecast was actually made)
Forecasted Date (the date and time the forecast is for)
Value (the actual value of the forecast)

So the Created Timestamp will change with each new spreadsheet that is
sent out even though the forecast will remain the same. I want to
write code so that the forecast will only be appended when the range
of the forecast changes.

I don't know if that makes sense, but any suggestions are welcome.
Thanks

Anna Warloe

[DFS]

Re: appending new data

Anna,

If the attachments come in via email in a consistent format (ie the same
data in the same place in the same file type) your life will be much easier.


1) set a primary key on your master table, so no duplicate data can be
added. You said it was river forecasts, but I don't see mention of "River"
in your table description.

PK on:
- RiverName
- Forecasted Date (the date and time the forecast is for)
- Value (the actual value of the forecast)

This will prevent duplicate forecasts from being added to your system. Only
the first CreatedDate will be stored in your data.

2) There's probably VB code to mine the attachments directly from the email
(especially if it's MS Outlook), but I don't know how. You can manually
save the email attachments into a "daily forecast new" directory on your
hard drive

3) write VBA code to process each file in the "new" directory, add the data
to your system, then move the files to a "daily forecast archived" directory


Alternatively, you can do all of steps 2 and 3 manually by importing each
email attachment into your database and appending the data to the master
table.




"Anna Warloe" <Anna.Warloe@Pa cifiCorp.com> wrote in message
news:e389116f.0 312011207.2ee1a cc8@posting.goo gle.com...[color=blue]
> This might be a dumb question, but I am pretty new to Access....
>
> I am trying to write code in Access to take river forecasts which are
> delivered as attachments in emails and append the data into an Access
> Database. Some forecasts are updated 4 times a day and others are
> only updated daily. I am trying to write a code which will only
> update the new forecasts and not cause repetitive data from the
> forecasts that only change once a day. (Some sort of "Append new data
> only" query). I have looked at some of the posts on here but I don't
> understand enough about access to fully understand what they are
> suggesting.
>
>
> Each record that is entered into my master table has the following
> fields:
>
> Created Timestamp (when the forecast was actually made)
> Forecasted Date (the date and time the forecast is for)
> Value (the actual value of the forecast)
>
> So the Created Timestamp will change with each new spreadsheet that is
> sent out even though the forecast will remain the same. I want to
> write code so that the forecast will only be appended when the range
> of the forecast changes.
>
> I don't know if that makes sense, but any suggestions are welcome.
> Thanks
>
> Anna Warloe[/color]