Count the number of imports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

M - 25 Feb 2004 16:40 GMT

I am importing tab-delimited files into temp tables using
Access 97. The imports are successful and working
smoothly. Once the tables are populated I run APPEND
queries to transfer the records to holding tables that
hold the data for historical purposes. I'd like to limit
the APPEND action to a once-per-day action. I've tried a
loop-conditional macro but have been unsuccessful with
this method. How can I implement a counter that counts the
number of imports in a day to control this? Possible in
this day and age? Either macro or VBA would be great.

Thanks

M

Reply to this Message

John Nurick - 25 Feb 2004 19:39 GMT

Hi M,

I'm not sure whether this can be done in a macro, but it certainly can
in VBA. I'd do it like this:

Create a table containing two fields
    tblVBARunDate
    ProcName    - text(20), primary key
    DateStamp    - date/time

Add one record to this table
    "RecordsTransferred", [yesterday's date]

(Maybe you already have a "settings" table in your database. If so,
that's where this should go.)

Then in your code, before you run the append queries, use the DLookup()
function to look up the RecordsTransferred date. If it is equal to
today's date, go no further. If it is less than today's date then run
the append queries. Finally, run a query that updates
tblVBATunDate.DateStamp to Date() where ProcName =
"RecordsTransferred".

>I am importing tab-delimited files into temp tables using
>Access 97. The imports are successful and working
[quoted text clipped - 10 lines]
>
>M

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

- 27 Feb 2004 13:49 GMT

Great. This will work fine. Thanks for all of your help.

M

>-----Original Message-----
>Hi M,
[quoted text clipped - 40 lines]
>Please respond in the newgroup and not by email.
>.

Reply to this Message