Solved

Bulk Insert  into MSAccess thru vb.net

Posted on 2014-02-23
4
921 Views
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  

 Sam
0
Comment
Question by:Sam OZ
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 200 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.


Kelvin
0
 
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).


Kelvin
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 100 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.
0
 

Author Closing Comment

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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

809 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