Go Premium for a chance to win a PS4. Enter to Win


Inserting large data files into Access database

Posted on 2015-02-10
Medium Priority
Last Modified: 2016-02-11
Hi Experts,

I receive 5 zip files in a daily basis. each zip file contains other zip files.
When we extract the last zip files we will get 50 text files
these text files contain millions of records, I want to inload these records into access database, then move the text file to history folder.

What is the fastest way to extract the text files from the zip files in one folder, loop in each file, and import the data into access database using VBA.
***speed is important***

Any advice?

Question by:Mohamed ElSheikh
  • 6
  • 6
  • 2
  • +3

Expert Comment

by:Matthew Borrusso
ID: 40602313
I know there will be a better way to do this.

I am going real low tech here.

Write a script that uses a command line zip/unzip utility.
use it to decompress down to the text files in a specified directory.
Then use something like http://support.microsoft.com/kb/230265 or http://www.databasedev.co.uk/text-import-macro.html

Supposedly, you can use some of the MySQL tools to pump text into access.

Again, low tech, I am sure one of the experts can give you a much better way to accomplish.

Author Comment

by:Mohamed ElSheikh
ID: 40602975
Thank you Matthew,
I am currently using code like what you mentioned above, but very slow to inload all of these files and taking hours.
could you please give me more information or instructions to use the MySQL tools?
can we use this tool using VBA?
what software do I need to install? wamp? or what
kind regards,
LVL 85
ID: 40602995
Text file handling in Access is somewhat limited, so you're not going to get much in the way of performance.

You might try using TransferText AFTER unzipping the files. Move the text files into an Access table, and then process them in that table. Assuming the import works properly (i.e. the resultant table is properly formed, with valid rows/columns) this would likely provide the best performance.

However, if performance is the major factor in this, then you might consider a different platform, like .NET.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 52

Expert Comment

by:Gustav Brock
ID: 40603738
What is slow? The unzipping or the import?

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40603792
Just some notes here.
Is this system meant to be temporary?
Because ...opening zip files, extracting text files and importing these text files to Access is not a very efficient way of updating a database.
There is just too much to go wrong...:

What if the zip file is corrupted?
What if a text file is not in the correct format?
Suppose a text file contains invalid data?
What if the code fails?
How are you ensuring that the code runs every day? (Suppose a day is missed?, suppose the code is inadvertently triggered twice in one day?)
How would you detect for these issues (and the many others that might occur)
How would you ...or the code, react if an error occurred?
How valuable is this data if it is lost, or inconsistent?

Complex systems like this work great when all goes well.
The real problems occur when things don't go as smoothly as you had hoped. (the above issues...)
So in the above scenarios, your code would need robust: Error Handling, Validation, and possibly Roll-Backs

< you can use some of the MySQL tools to pump text into access>
...<<can we use this tool using VBA?>
From what I know of MYSQL, these tools will mostly pull data ino *MySQL databases*, not into Access directly...
Being MySQL tools, they are probably not going to be easily automated with Access VBA.
(Perhaps you may be able do something with .net ...but I am not sure...)

The other issue is your requirement for "speed".
While "hours" certainly seems like a long time, I can’t see a complex/robust system like this ever being "Fast"

So while you may get help here in doing this ...your long term goal should look into having a more efficient way of getting this data into Access.


Author Comment

by:Mohamed ElSheikh
ID: 40604749
Hi Scott McDaniel, Jeffrey Coachman
thank you for your try to help
actually the company does not have budget to buy SQL server or any other tools. I wish if I can use SSIS to import data, into SQL server, but for limited resources I have to use VBA or any other free resources
What i understood that MySQL is free, and we can setup the server by installing WAMP. am I right? do you know if the MySQL importing tool is free or not?
thank you all for your help and valuable information

I mean importing data

LVL 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 2000 total points
ID: 40605089
OK. Then - if you stick with MySQL - is the command LOAD DATA INFILE:

From a test I did in 2006:
When inserting records by a pass-through query from Access, I've found that reading the records off a text file using


into an empty non-indexed table runs at about 50,000 records per second even using very modest hardware.
This is way faster than any normal "Insert Into ... " I've tried.

To run MySQL, you wouldn't need a WAMP setup; that's intended for web usage. MySQL did run on even Windows NT, don't about the newer versions but Windows 7 should be fine - if you wish to refrain from Linux servers.

LVL 85
ID: 40605350
actually the company does not have budget to buy SQL server or any other tools
SQL Server Express is free, and comes with Management Studio to allow you to run queries, create databases/tables, etc.

But as gustav has indicated, you can do this in Access. if you determine where the bottleneck is.

Does the slowdown occur during the unzip process, or during the import process?

If it's the unzip process, then I doubt there's much you can do about that.

If it's the import process, then gustav's suggestion may be the way to go - but you'd have to test to determine that.
LVL 24

Expert Comment

ID: 40609003

you can use SQL directly to import data, for this purpose a SCHEMA.INI file can be created which describes the columns to import.

A good description of this theme can be found here:
Working with external text files in MS Access

In SQL Server this can also be done using OPENROWSET.



Author Comment

by:Mohamed ElSheikh
ID: 40615249
Thank you all for your help.
I am trying to use MySQL "Load Data Local Infile" and really is is very fast
But i have an issue, when i write the T-SQL in SQL tab i have no issue, but when i write same t-sql into stored procedure, and compile it i got an error message "LOAD DATA is not allowed in stored procedures"

I also install the MySQL ODBC drive in order to link the MySQL table to MS Access
I imported one text file (1.7GB) and it took 5 minutes which is great, but when i create access query to git gust top 100 records i got an error message "Out of memory"

any advise for both issues? load the data is really very fast in SQL tab, but not supported in procedure or manipulating the data in access

please advise

kind regards,
LVL 52

Expert Comment

by:Gustav Brock
ID: 40615281
A note: You mention T-SQL but I believe that is the specific SQL dialect for SQL Server, not for MySQL.

But do you really need a stored procedure? Can't you write plain SQL and feed it to the server with one or more pass-through queries?
Also, to retrieve records, I would use pass-through queries and the specific SQL dialect of MySQL.
Finally, check that you haven't created fields of Big Integers or other data types that Access doesn't understand.


Author Comment

by:Mohamed ElSheikh
ID: 40615521
Thank you Gustav

I am using the MySQL syntax and I have no issue to run the code from phpMyAdmin. the only thing i want to schedule importing data to run automatically and this is why I want to create a procedure

I am able to query the table for small amount of data (300k records), but i am not able to query the big table (7.5 million) using where conditions

what do you mean by pass-through query?

LVL 52

Accepted Solution

Gustav Brock earned 2000 total points
ID: 40616176
That's a query with server specific SQL (not validated by Access) which you send via the ODBC driver directly to the server; it then runs on the server and - for select queries - return a read-only result set.

So you can build your query in phpMyAdmin and copy/paste to Access. However, if it won't in phpMyAdmin, neither it will in Access.

Here's an obtuse guide:
Create Pass-Through Query


Author Comment

by:Mohamed ElSheikh
ID: 40753955
LVL 52

Expert Comment

by:Gustav Brock
ID: 40754234
You are welcome!

Did you succeed?


Author Closing Comment

by:Mohamed ElSheikh
ID: 40754451
Hi Gustav Brock
the command you mentioned above "LOAD DATA INFILE" MySQL is very helpful and i can load the 4GB of text files into indexed tables in 5 to 10 minutes and without index in just 3 minutes which is great
the only issue I have right now is unzipping files is slow. any way can speed the extracting by vba?
last point how do you install MySQL without wamp?
thanks again for your help
LVL 52

Expert Comment

by:Gustav Brock
ID: 40754457
The download for MySQL is here:


As for the unzipping, I don't know how you do it right now, but numerous tools - all claiming to be fast - is available. Like this:


I guess you just have to check out some of these.


Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question