Bulk Insert  into MSAccess thru vb.net

Posted on 2014-02-23
Medium Priority
Last Modified: 2014-02-24
Hi Experts,

   I am developing a vb.net routine to  get data from oracle database (from multiple tables)   into a single Table of MS Access  ( 2003 Enterprise)

The pseudo code is like

    Query Oracle
    Get result in a OleReader
     Loop thru each row
        Insert Single row  into  Access Table  with OleDbcommand (using Sql Insert statement)
     End Loop

  The operation takes 15-20 minutes ( If performance can improve , it is desirable . But I am happy with it even otherwise )
 My real  issues is , the query result from oracle  has  700-800 thousands of  records .  
 As the routine is running , the Access size is increasing . And finally ,  it doesn't insert rows ( certainly  , the 2GB limit on MS access is the culprit)  . But when I compress, it reduces the size to 500 MB  .
 But I can't do this compression dynamically thru  the code  when the code is running (because the Table is locked)

  If there is some bulk insert source code  ( that meets my purpose instead of looping thru each rows )  , which will not increase the size dramatically - That is what I am looking for .

 Also, if the compression someway can be done dynamically ( which I really doubt can be done) , that also can be a solution

  Appreciate  your help  

Question by:Sam OZ
  • 2
LVL 22

Accepted Solution

Kelvin Sparks earned 800 total points
ID: 39881664
I have struck that as well (many years ago). The size comes from Access paging the data table as records come in to allow data to be "inserted" according to indexing. Check the target table and remove all indexes, other than the PK.  That MAY help you.

LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39881665
If there are any relationships on the table, drop them as well (add them all back later).

LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 400 total points
ID: 39881753
There is an UpdateBatchSize that you can set on a DataAdapter, but it works only with databases that supports Batch inserts, and Access does not. This is one of the many limitations of Access.

And the reason that you cannot compress by default because the database must be closed and not in use when you do so. And ADO.NET has a connection pooling mechanism (you can look up "connection pooling" in the documentation if you are curious) that keeps the connection opened for a while (60 seconds by default) even when you Close it explicitely in your code.

The solution would be to disable connection pooling and close the connection / compress / reopen the connection from time to time during your update. But unfortunately, once again, it is not possible with Access because the OleDBConnection does not have the ClearPool method that is available in other ADO.NET classes such as the SqlConnection.

If you want to be working with that amount of data, you will end up being frustrated by the limitations of Access. Why not move your stuff to SQL Server Express? It's free and more powerful. And if need be, Access can connect to it and be used almost as usual for Forms and Reports.

Author Closing Comment

by:Sam OZ
ID: 39881860
Great and informative  . The actual issue was  the indexes . It is fine now

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

623 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