Importing files when updated automatically.

[David]

Hi there.

Apologies in advance if I should have posted this elsewhere, this seemed
like the most appropriate...let me know if you have a suggestion for a
different newsgroup.

I have created a database which, amongst many other things, imports two
files from our network server, and then output two other files (derived data
from the inputs). Basically, one file shows up on the server around 5:00pm,
while the other one doesn't tend to show up until around midnight.

My import procedures are modules in Access 2000 VBA, as they are not simple
flat files and require considerable conversion.

I want to automate the process so that everyday SOMETHING polls the
directory where the files are stored, and when a new one shows up (i.e. the
file date/time change), it kicks off the import process. I would much prefer
to do this than have to mandate someone in my organization to manually have
to push buttons (which also creates problems if, say, they are sick)...

Can anyone provide some advice on how I might do this? I'm normally very
good with coming up with solutions...but really am sort of stumped on this
one.

Thanks in advance for your attention, most appreciated.

David

 

[Dirk Goldgar]

Hi there.

Apologies in advance if I should have posted this elsewhere, this
seemed like the most appropriate...let me know if you have a
suggestion for a different newsgroup.

I have created a database which, amongst many other things, imports
two files from our network server, and then output two other files
(derived data from the inputs). Basically, one file shows up on the
server around 5:00pm, while the other one doesn't tend to show up
until around midnight.

My import procedures are modules in Access 2000 VBA, as they are not
simple flat files and require considerable conversion.

I want to automate the process so that everyday SOMETHING polls the
directory where the files are stored, and when a new one shows up
(i.e. the file date/time change), it kicks off the import process. I
would much prefer to do this than have to mandate someone in my
organization to manually have to push buttons (which also creates
problems if, say, they are sick)...

Can anyone provide some advice on how I might do this? I'm normally
very good with coming up with solutions...but really am sort of
stumped on this one.

Thanks in advance for your attention, most appreciated.

First, you'll need a table to store the FileDateTime of each file that
was last imported, so your code can check whether there's a new version
to be imported. Second, you'll need something to check the relevant
directory and trigger the process if there's a new file there.

Can you count on the database to be open at whatever time the import
might need to be done? If so, you could use the Timer event of a form
that will remain open at all times (maybe hidden), with a TimerInterval
set to fire every so often. The Timer event procedure would check
whether there's a new file out there to be imported. If there is, it
would run the import process and, upon successful completion, record the
FileDateTime in the LastImport table.

That's pretty simple, but I can think of two considerations you have to
work around. As I said, the database has to be open so that the Timer
event can fire. If that's not necessarily the case, you need an
external scheduler routine to open the database, and if you do that, the
scheduler can determine whether there's a file to be imported and open
the database with a command-line option to run a macro to trigger the
import. Then. of course, you don't even need the Timer event procedure,
since the scheduler would be telling the application to run the import.

The second consideration that occurs to me is that., if you do use the
Timer event, then if you have multiple copies of the database open, only
one of them should be performing the import. So you need to take steps
to prevent to different instances of the application from trying to
import the file at the same time. That can probably be handled by
updating the import control table with an "import in progress" flag,
along with the file date/time, when you start the import, and locking
that table while you update it.