none
Access database corrupted when multiple users access it through VB.NET application

    Question

  • Hi Gurus,

     

    I’m maintaining an application that developed using VB.NET 2005 and its database is Access 2003. The database is in a shared location and client applications access this database over the network. Database configuration are defined on the app.config file. The application uses OledbProvides to access the database and on each database call connection open and close once it is done. (Every database call method having open and close connection statements).  The application’s peek concurrent user base is around 40 (does add/update/delete operations).

     

    The main issue with me is “Frequently this database is getting corrupted and I have to make it repair through the Compress & Repair option on the Access itself”.

     

    I have tried with many possible ways, But still I don’t know why this database corruption happen, could this be due too many concurrent users? Or Due to the versions not compatible with the clients that uses this databases? Or any other thing… how can I avoid this database corruption.?

     

    Thx

    NSJSL


    • Edited by NSJSL Tuesday, December 27, 2011 5:10 AM found some unwanted letters at the top
    Tuesday, December 27, 2011 5:09 AM

All replies

  • I'm not usre if your problem is with the connection string not being correct or you may need to add locks to your transactions.  See this webpage for an explanation of lock

    http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx


    jdweng
    Tuesday, December 27, 2011 5:19 AM
  • Thx for the update. Well, the connection string is correctly set and this corruption happens when it is being using by the users... will check the given URL.

     

    BTW this application uses access database not SQL


    nilantha
    • Edited by NSJSL Tuesday, December 27, 2011 5:24 AM more text
    Tuesday, December 27, 2011 5:22 AM
  • Hi,

    do you have any WAN segments in the network or do all the users are within the LAN?


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Tuesday, December 27, 2011 5:40 AM
  • There are WAN users too...

    nilantha
    Tuesday, December 27, 2011 5:41 AM
  • I bet they are the reason for your corruption. I had an experience of 100+ users within a large network (90% were WAN) and the corruption appeared ~ once a week. Since this is already a VB.NET front-end you've already done the every possible work here. The only real way is to change a back-end platform, SQL Server Express, for example, if it is possible for you.

    You might be interested in more detailed explanation here http://kallal.ca/Wan/Wans.html


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Tuesday, December 27, 2011 6:20 AM
  • Yeh, me too now believe connection failures may cause this issue.... Now I'm thinking a way to close connection gracefully upon connection failure.

    nilantha
    Tuesday, December 27, 2011 6:43 AM
  • Andrey : Lets simplifier this problem.  What Albert D. Kallal is saying that he doesn't know why the Access Locking mechanism isn't working properly.  Locks in a database will normally have time-Outs and Albert is speculating with a database that is being used over a LAN that the timeouts are expiring and causing the database to get corrupted.

    Albert is saying that Access (or the Microsoft Jet Engine) has bugs and that the only solution is to move to a SQL Server that doesn't have these bugs.

    Problems like these are normally solved by changing the locking mechanism to prevent the errors.  I don't have the experience to know if adding some additional locks to the existing database will solve the problem, or extending the timeouts will solve the problem. I think in this case it may be worth some effort to attempt to fix the problem.  Maybe contacting Albert may be useful.

     

    The following webpage gives an example of locking a row

    http://support.microsoft.com/kb/306435

    More locking information

    http://msdn.microsoft.com/en-us/library/aa189633(v=office.10).aspx

     

    Setting the lock mode option may solve the problemn like in the connection string  below.

    Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source=D:\Dev\Reports.mdb;Mode=Share Deny Write;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False


    jdweng
    Tuesday, December 27, 2011 6:50 AM
  • Joel,

    from my experience the type of lock doesn't affect the final result. I can't discuss at the low technical level, but I do believe "Access" and "not stable connection" can't play together.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Tuesday, December 27, 2011 7:17 AM
  • Yeah, that's right.  For using Access as the backend database, the underlying network must be rock solid.

    Even on a pure LAN, if anything on the network goes flaky, chances are very high that any Access/JET database running on it will get corrupted and when the WAN is present, trying to use a MDB or ACCDB database is really running after trouble.

    If this database is mission critical for the company, you should really give SQL-Server Express a go.

    --
    Sylvain Lafontaine

    Tuesday, December 27, 2011 9:22 AM
  • The locks need to be enabled on the Microsoft Jet Engine and all clients need to use the same Jet Engine for tracking the locks.  I'm not sure if this is possible.
    jdweng
    Tuesday, December 27, 2011 1:18 PM
  • NSJSL wrote:

    Yeh, me too now believe connection failures may cause this issue.... Now I'm thinking a way to close connection gracefully upon connection failure.

    The problem though is if the network gets interrupted while your program
    is doing the database write/append/delete.   Just because you might be
    able to gracefully handle the error doesn't mean that the corruption
    hasn't already happened.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Saturday, December 31, 2011 12:16 AM
  • Joel Engineer wrote:

    Andrey : Lets simplifier this problem.  What Albert D. Kallal is saying that he doesn't know why the Access Locking mechanism isn't working properly.  Locks in a database will normally have time-Outs and Albert is speculating with a database that is being used over a LAN that the timeouts are expiring and causing the database to get corrupted.

    Don't put words in Albert's keyboard.  Please quote the sentence where
    Albert mentions the word timeout

    Albert is saying that Access (or the Microsoft Jet Engine) has bugs and that the only solution is to move to a SQL Server that doesn't have these bugs.

    No Albert isn't saying that Access/Jet has bugs.  If he is please quote
    the exact sentence.

    This problem is the same as it is with any file based system.  If the
    connection gets dropped while in the middle of updates bad things
    happen.

    SQL Server and similar database tools handle this because they have a
    program on the server which does the actual updates to the database
    files on that server.

    Problems like these are normally solved by changing the locking mechanism to prevent the errors.

    No the locking mechansim has nothing to do with this.   If it's a WAN or
    wireless networking is involved and you are getting corruptions then
    move to SQL Server or similar.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Saturday, December 31, 2011 12:22 AM
  • Tony: I have a Master of Computer Science from Steven's Institute of Technology and my "Theory of Database Course" was taught by an employee of Bell Labs/AT&T (MA BELL).  Bell Labs developed the databases that were used for your phone bills, by operators to look up phone numbers, and by the switching networks that connect your phone calls.  These database couldn't afford to get corrupted the way Access is getting corrupted.  The Bell Labs databases operated over the internet long before most people have ever heard of the internet.

    Don't tell me that these problems can't be solved.  A database should validate the data before an update is performed and reject the transaction if the Wan get dropped.


    jdweng
    Saturday, December 31, 2011 1:51 AM
  • Not to poke too much fun but is this why my phone bills are always screwed up?
    Chris Ward
    Saturday, December 31, 2011 5:05 AM
  • Joel Engineer wrote:

    Tony: I have a Master of Computer Science from Steven's Institute of Technology and my "Theory of Database Course" was taught by an employee of Bell Labs/AT&T (MA BELL).  Bell Labs developed the databases that were used for your phone bills, by operators to look up phone numbers, and by the switching networks that connect your phone calls.  These database couldn't afford to get corrupted the way Access is getting corrupted.  The Bell Labs databases operated over the internet long before most people have ever heard of the internet.

    I don't particularly care where you got your degree or who taught you.

    Don't tell me that these problems can't be solved.  A database should validate the data before an update is performed and reject the transaction if the Wan get dropped.

    A client server database such as SQL Server can and does do exactly this
    because it runs on the server.   But an Access MDB file, or other file
    based system, can't do this because the connection is lost between
    Access and the file server.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Saturday, December 31, 2011 10:32 PM
  • Tony: There are reasons why companies like Oracle at still in business.  there are reason why IBM is making large profits providing IT services even in a depressed economy.  there are reasons the comapnaies look at Oracle and IBM when developing large distributed  networks, and don't even consider Microsoft!

    I have had discussion with Albert D. Kallal on the performance of the Microsoft SQL Server and don,t agree with his analysis.  According to Albert the Microsoft SQL Server stores all the data in XML format which is text.  Traditional databases stores data in binary format.  Alberts opinion is that XML databases have the same or better performance compared to binary databases.  I would like to see detailed data showing this is true?

    Bill Gates and Microsoft are strong believes in Moore's Law that computer hardware will double in size and speed every two years, therefore, software doesn't have to be effiecient because the hardware will compensate for the deficiencies in the software.  Bill Gates developed a company to make a profit, not one to develope efficient software that runs quickly without bugs.  Everytime I sit at my computer waiting for a large file to open, or waiting for my Microsoft IE Explorer to connect to connect to a website I think of Bill Gates.


    jdweng
    Sunday, January 01, 2012 4:12 AM
  • Joel Engineer wrote:

    Tony: There are reasons why companies like Oracle at still in business.  there are reason why IBM is making large profits providing IT services even in a depressed economy.  there are reasons the comapnaies look at Oracle and IBM when developing large distributed  networks, and don't even consider Microsoft!

    Irrelevant to the discussion.

    I have had discussion with Albert D. Kallal on the performance of the Microsoft SQL Server and don,t agree with his analysis.  According to Albert the Microsoft SQL Server stores all the data in XML format which is text.  Traditional databases stores data in binary format.  Alberts opinion is that XML databases have the same or better performance compared to binary databases.  I would like to see detailed data showing this is true?

    Quote the discussion where Albert states that.  I know you've stated
    that in the past a number of times and I've strenuously disagreed with
    you.

    Bill Gates and Microsoft are strong believes in Moore's Law that computer hardware will double in size and speed every two years, therefore, software doesn't have to be effiecient because the hardware will compensate for thedeficiencies in the software.  Bill Gates developed a company to make a profit, not one to develope efficient software that runs quickly without bugs.  Everytime I sit at my computer waiting for a large file to open, or waiting for my Microsoft IE Explorer to connect to connect to a website I think of Bill Gates.

    Irrelevant to the discussion.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Sunday, January 01, 2012 9:01 PM