Import text files into MS access automatically

Question

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 21-Dec-21 2:29am

Deniskate

Updated 21-Dec-21 3:15am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-12-21T03:15:00

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