[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1363
  • Last Modified:

VB.NET Inserting into multiple tables of MS Access simultaneously

Hi ,

   I have a vb.net application reading an oracle database and writing to an Access Database .
Hi Experts,
 
   I have two sets of Tables , So I was using  two instances of the same application to insert ( One instance to insert one set of tables and another instance to insert the second set)

  The problem is , Access file size increases over 2 GB   ( In fact if I use  one instance  of EXE  to insert all the tables , I never come thru this issue ) .
  How can I solve this ?
 Is there really a restriction on Access preventing insert into two tables at same time ?
 (Also out of curiosity , is this same for updating tables also)

Thanks
    Sam
0
Sam OZ
Asked:
Sam OZ
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access size is limited to 2 GB, so be aware of this - you're essentially at the upper limit of Access, and need to be very careful.

There is no "restriction" in this regard, but your architecture isn't really clear. When you say two "instances", do you mean that two people have the application open at one time?

Are you ONLY inserting in the Access database?

Where are the two "sets" of tables located?
0
 
Sam OZAuthor Commented:
Hi ,

      Just additional details on my architecture

        1) I am using two instances  of the same application to write to the same Access database
             ( In one instance Tb1 , Tb2, Tb3  are inserted .  In the other instance Tb11, Tb22 , Tb33 are inserted)

        2)  If I do the same thing in just one instance ( Insert Tb1,2,3, 11,22,33 )   the size is only around 200 MB) .... But something goes wrong when multiple insertion happens

    I have not used Transaction object to do this . So Is there a problem when both instances trying to write to the same Database ?

Thanks
   Sam
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, there should not be a problem. Multiple "instances" of a program can connect to an Access database, and the number of connections should have no bearing on the size of the database.

What sort of data are you inserting?

Have you Compacted the database?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Sam OZAuthor Commented:
Hi ,
    The fields are just Text  and  one memo field in each table .
    If I compact it , the size definitely  reduces . But my problem is that it is getting  inserted thru a VB.NET application  and   in the middle  of it the size turns out bigger . So I can't compact it as the insert is going on ( As I stated before , it is  happening  only when two instances of the .net application trying to insert and something like a Lock comes , and size increases ..... )
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you show the code you're using to insert?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
With multiple "users", your database may bloat quickly in size, especially if your simply inserting.

In order to avoid concurrency issues, JET will always use a fresh page on an insert rather than look for a page that may have sufficient space.

You've also added in a memo field, which complicates things as memo's and OLE fields are stored separate from the rest of the record if the field size is >30 bytes.

Wrapping your inserts in a transaction may help.

Jim.
0
 
sarabandeCommented:
Wrapping your inserts in a transaction may help.
don't think so. transactions would allow to rollback all the inserted records at any time. this option costs additional storage and should increase concurrency issues.

generally, I would assume that two instances running and writing to the same access db is slower than one instance.

for optimizing I would use 3 threads, two of them reading from oracle and putting their records to a queue. a third thread would open the access db exclusively, fetch (and delete) records from queue and write them.

Sara
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
@Sara,

 <<don't think so. transactions would allow to rollback all the inserted records at any time. this option costs additional storage and should increase concurrency issues. >>

  It's easy enough to try and I would suspect that once a page is grabbed and locked, JET would fill it rather than grab another page.  I've never checked that directly though, which is why I said they might.

 With transactions in place, the insert processing is totally different.

 If it does, then they can do it in batches of 5-10K records at a time (which would be similar in concept to what you suggested)  to avoid any major concurrency issues.

Jim.
0
 
sarabandeCommented:
@Jim

I see you argument, but as the access database is file-based the jet engine has not much chance to avoid a page conflict. in case of two transactions running parallel it might be able to fill the "temporary" pages for a transaction with more than one record and perform a page lock only when writing the full page but actually I doubt that they put so much logic into a case (concurrency of two clients writing to the same database file) which could not be handled properly because of the missing server (process) and where the two clients are not synchronized at all. it may have a reason when the 'database repair' function is in the main menu of a database application.

but you are right. it is worth a try.

Sara
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<in case of two transactions running parallel it might be able to fill the "temporary" pages for a transaction with more than one record and perform a page lock only when writing the full page but actually I doubt that they put so much logic into a case >>

You'd be surprised what's in JET<g>.  But it's not so much the two transactions running parallel (each does not know about the other), but that when you use transactions the process is totally different.  All the changes are buffered to a temp table (~JETxxxxxx in the temp directory) and locks are help on the pages as they are being modified.  That's what allows the rollback.

 What I don't know for sure however is if the same page allocation rule is followed or not.

 Being that a transaction is viewed as a single operation, the opportunity is there for them to keep filling a page since they already have a lock on it and will be maintaining that lock until the transaction is finished.

 Worth a try though.

Jim.
0
 
sarabandeCommented:
You'd be surprised what's in JET<g>.  
probably. it is some time ago I had to care for concurrency in an access database.

if they use the same temporary file for both the clients, page locking is required for the temporary pages and for merging the temporary pages to the database file after commit. anyway if a page lock error occurs, it is much more difficult for a single client to handle it as it would be with a database server.

I am curious whether it works as expected and how fast it will be.

Sara
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<if they use the same temporary file for both the clients,>>

 It can't....all DB processing is carried out by each client....there's no server side process.  On a single client, two processes are considered and work as two separate users.

 Nothing is ever shared between instances of MSACCESS.EXE.  That includes the JET cache as well as any temp files.

Jim.
0
 
Sam OZAuthor Commented:
Hi ,
       My simple  test tells me that Transaction object also doesn't  help .  It  looks to me since Access is file based  , it may be an issue if  multiple inserts are tried concurrently  ( This is Access 2003 . I don't know that has any significance)

  At least to me ,  running  multiple instances  of EXE helps .... There may be slight slowdown on individual application But  instead of  5 + 3 = 8 hrs  , It would come around 5.5 hours

 Anyway, I think I will go to my original plan of splitting the Database into 2  so that the concurrency issue never arises .
 
 Thanks Everyone
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now