Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation and Batch Processing (Access 2000 + SP3)

    Hi all,

    I have a question about Access/ VBA that I'm unsure about. It's basically a case of finding out if Access is fit for a job I want doing.

    I have a directory where text files (comma delimited) are dumped by another program.

    I can read these text files into Access, no problems. I then need to run a query on them and push the results out to a comma delimited text file.

    I know this can all be done in a reasonably straight forward way by doing it manually. However, my question is, can it be done automatically? I need this to run (for example) every day at midnight. Access needs to import a directory of text files (all of which have different filenames each day), query the data and then output a comma delimiited text file containing the results of the query.

    Can this be done with Access? Would anyone recommend a better tool for the job? I'd definately like a database back-end as this task will eventually be expanded (a user-friendly database front-end will want writing).

    Thanks in advance,
    Rgds,
    Chris.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Automation and Batch Processing (Access 2000 + SP3)

    It is quite possible to write a VBA procedure to process all text files in a specific folder and export another text file. There are several ways you could have it run at midnight:

    Create a form in the database, with code in the On Timer event that checks the date and time periodically. When the date changes, run the procedure to process the text files. This method requires that you leave the database open permanently, with the form loaded.

    Alternatively, you could call the procedure in a macro named AutoExec, so that it is executed when the database is opened. The procedure would make Access quit when done. Create a scheduled job in Windows to open the database at midnight.

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation and Batch Processing (Access 2000 + SP3)

    Thanks Hans.

    I though I'd check as I was talking to a friend last night who was saying Access wasn't very good at batch processing/ automation and that I should use VB instead.

    Thanks,
    Chris.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation and Batch Processing (Access 2000 + SP3)

    It isn't Access that handles it, it's VBA. I suspect your friend was thinking not of batch processing but scheduling, which is trickier in Access than in VB.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •