Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Bulk Insert  into MSAccess thru vb.net

Posted on 2014-02-23
4
Medium Priority
?
995 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

Accepted Solution

by:
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.


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 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.
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

618 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