• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1013
  • Last Modified:

Bulk Insert into MSAccess thru vb.net

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
Sam OZ
Asked:
Sam OZ
  • 2
2 Solutions
 
Kelvin SparksCommented:
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
 
Kelvin SparksCommented:
If there are any relationships on the table, drop them as well (add them all back later).


Kelvin
0
 
Jacques Bourgeois (James Burger)Commented:
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
 
Sam OZAuthor Commented:
Great and informative  . The actual issue was  the indexes . It is fine now
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now