real-time data integration of csv file into access

[nlsoe]

Hello all,

I'm wondering if there is a tool in MS Access which can be watching a folder (just like an event listener) for any (new) file. The folder can receive a csv file any moment. As soon as a new csv file has arrived in the folder, this tool will detect it and extract the content of the file to be integrated into an existing Access database. Of course, the csv files will adhere to a pre-defined format.

If there is such a tool or feature in Access, would you point me to the right direction please? Thanks in advance.

Nay

[June7]

There is no such tool in Access. Might be able to program code to check the folder on a schedule (hourly?) but I think 'timers' in VBA are not easy. I have never done.

[rpeare]

I agree with june, running a real time update is a very, very resource heavy endeavor. You literally have to have the code check a folder every x seconds for files and it doesn't run if the application isn't open. If you are trying to update information efficiently you may want to consider another option of say, having a powershell script running on your server that will push any new data into the appropriate table and run that every 5 minutes or so. Then the server is doing the bulk of the work while the database running on your front end isn't consumed with measuring time and slowing your whole application down.

Barring that (forcing data in from the server side rather than the PC side), June's suggestion is likely the best one, you can have a button that imports any information that's currently in a designated folder.

[nlsoe]

Thanks June and rpeare. My situation is more like a partnership of 2 services instead of a client-sever. So the server pushing isn't an option for me. And the service requirement isn't 24/7, so I don't mind starting the 'application' manually, but once it is running, I would like it to do the data polling automatically (without any user intervention). It seems that if I like to avoid the use of timer, my only option may be developing a .NET app (there is 'File System Watcher' Class in .NET that can raise event once there is any change in a designated folder).

[rpeare]

Is it possible for you to have a stand alone machine that's just a dummy terminal. If it is you can build a database that will do all the timing and testing for new information.

Secondly you probably do not want to monitor change in a folder what I would likely do is let's say you start with 2 files in your target folder, when your process runs you would move those two files to a 'processed' folder so that anything in the base folder automatically gets processed rather than having to try and determine which is new and which isn't. And if you're in an environment where this type of live updating is important I'm guessing you're going to end up with a lot of files processed which makes the checking for changes more and more cumbersome.

File manipulation in MS access is handled easiest (in my opinion) with the filesystemobject method, look into that for your actual file opening, reading, moving, copying etc.