Count the number of imports

  • Thread starter Thread starter M
  • Start date Start date
M

M

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
 
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".
 
Great. This will work fine. Thanks for all of your help.

M

-----Original Message-----
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
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
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top