How to import large text file into access db?

I have to import large text file(about 10 to 15 mb) into access database. The file is in fixed width format.
Each line contains one record. As it is not a simple "Just dump into" import but requires some intermediate calculations and updation into 4 different tables.
I have wriiten a project for this but it takes lot of time to finish the process and seems that computer is hanged up.
If anybody know any better strategy or a sleek way to do this then please let know.
I have asked this thing before also but didn't get any better suggestions.
Thanx a lot in advance.
Who is Participating?
vindevogelConnect With a Mentor Commented:
This answer is valid if your fixed width of the columns is always the same, otherwise, it's garbage.

1)  Create an Access MDB.
2)  Link your flat file into your DB via File - Get External Data - Link File
3)  Choose Text File in the options and select your file.
4)  Say it's fixed width and specify the width for each of the fields in the Link Table Wizard.  You'll then have a table (your flat file) which you can query and so.
5)  If your functions are a little to complex, add all your functions in a Module (program them there)
6)  Create the 4 export tables you need (or if necessary, create them in another DB and link them too)
7)  Create 4 append queries to update your 4 tables.

I've done this with a flat file of 2000 lines, containing 9 fields per row.  There are extracted into 2 tables, but with a join on another external file (Excel file).  The update queries take about 2 seconds to complete.

That I find fast, considering I'm using TXT files and Excel files to get my data into the DB.

Give it a try ...

Use a VB program to read the text file line by line using

open "file" for input as #1
while not(eof(1))
Input #1,sLine

''  parse data and save in Access .mdb


DoEvents means you can multitask in windows without locking the PC

khatrijAuthor Commented:
I have to write it thru vb.
thanx for yer comments.

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.
Thank you,
Moderator @ Experts Exchange
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.