Click here to Skip to main content
15,311,717 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello gyus,
I have a folder which has many text files with tables (delimited with fixed width) this folder is updated daily 3 times.
I want a batch file script or any way to move these text files into microsoft access database file .mdb automatically.

what i was able to do is i created the database and imported the file into it but i dont know how to do this automatically daily 3 times.

Thanking very much in advanced

What I have tried:

I have a folder which has many text files with tables (delimited with fixed width) this folder is updated daily 3 times. I want a batch file script or any way to move these text files into microsoft access database file .mdb automatically.

what i was able to do is i created the database and imported the file into it but i dont know how to do this automatically daily 3 times.

Thanking very much in advanced
Posted
Updated 21-Dec-21 3:15am
Comments
Richard MacCutchan 21-Dec-21 8:49am
   
Create a batch script to do the import, and use task scheduler to run it at the appropriate times.
Deniskate 21-Dec-21 9:10am
   
Thanks for your reply but can you please give me a sample of the script as i never done this before
Richard MacCutchan 21-Dec-21 9:18am
   
Sorry, nor have I. I would start with the MSAccess documentation to see what tools are available for importing data. It may be that you need to write a VBA macro to read the file and update it that way.

1 solution

I used to do this.

1. Write some VBA to read the contents of a text file into a table. There are many examples on the internet e.g. VBA procedure to import csv file into access[^]

2. Write another VBA function to list the files in the folder Microsoft Access tips: List files recursively[^]

3. You will need some code to move the files out of the processing area once successfully loaded and I suggest also having some code that will validate that you have loaded all of the file content.

4. You can opt to have the code run as soon as you open the Access database e.g. Create a macro that runs when you open a database[^] or possibly better would be to use a command line switch (/x) - see Command-line switches for Microsoft Office products[^]

5. Schedule the process to run when you want it to using Windows Task Scheduler How to create an automated task using Task Scheduler on Windows 10 | Windows Central[^]

As an aside, .mdb is a very old version of Access database format - the very old versions can no longer be opened in modern versions of Access, and although the latest version of Office will still allow you to open later MDB files (Office 2000 onwards) there is no guarantee that Microsoft will continue to support that. My recommendation is to upgrade your Office installation if you possibly can and start converting your Access database(s) to .ACCDB
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900