Solved

Database getting corrupted ??

Posted on 2002-05-02
21
287 Views
Last Modified: 2010-05-02
Hi
I have about 40 users on a network, i have written a front end in visual basic which is then on all the users pc's. Then the database file is in a sharable directory in the server. I am using dao and Access97.
my database is about 50 megs with 4 tables my one table has 240 000 records. now it is getting corrupted intermitantly
can anyone shed some light here or something i can try
do i have to many users accessing the database file at once ?

thanx

Craig
0
Comment
Question by:CraigLazar
  • 6
  • 4
  • 3
  • +5
21 Comments
 
LVL 18

Expert Comment

by:deighton
ID: 6986434
are you using transactions?

Once all the users are logged off, does the .ldb file disappear?
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 6986458
no
the .ldb file is constantly there
0
 
LVL 18

Expert Comment

by:deighton
ID: 6986522
something might be left hanging around, some uncommited transactions, changes or other updates.  I thought the .ldb was supposed to go away if all was ok.  
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6987192
We have a similar problem with our Access database.  Our experience leads us to believe it results when network traffic is high or congested.  Some days we just know the database will go down because network response is so slow.

Another sympton also seems to be related to size.  After we repair and compact, the database will be about 15mb.  If it grows to 25-30mb we seem to have more database crashes.

One thing we did was to run another Access database that backs ours up every hour (simply copies it to another location) and then at about 3am we unshare the directory and repair and compact the database in addition to backing it up.  This ensures the database starts each day repaired and compacted.  Before we did that we were having crashes all the time.  Since then we only crash occasionally.

Usually a repair and compact fixes the problem.

good luck
mlmcc
0
 
LVL 14

Expert Comment

by:Matti
ID: 6987316
Hi!

That ldb shuld be deleted if you have JET3.X as all users have closed that db, why that don't get deleted try to work out this. Get the users name whit LDBView.exe and see the drivers installed on this workstation.  

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q176670

Read the Jetlock.doc

Win Socket version can cause trouble, the older one if your network has still these old files in use, dependence the protocol installed ofcause.


Matti
   
0
 
LVL 10

Expert Comment

by:arana
ID: 6987384
i dont think is the MDB size, i had problems with one MDB and it is over 60MB (but problem was not size) one of the tables has more than 700,000 records.

had the same problem days ago with one of my clients, less than 10 users on that one but mdb is above 60 MB (compressed) i was having same problem everyweek , but sometimes it didnt happen, i found the cause and ended up removing one of the PC's, problems disappeared, so i reinstalled windows and everything else on that PC disappeared, also changed network card.

but when this happened i already had created an autorepair program scheduled in the server for rapairing everunight at 2 am , and compacting every sunday at 2 am, now i dont need those anymore, but they still there just in case.
 
tip: remove the ldb file manually (recomended by MS)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 6987689
arana
What was the cause?

Possible reason ldb file isn't released is users just shutting down instead of properly exiting the database.  We have users who routinely just ctrl-alt-delete to exit.
We are finding this to be a frequent problem as we switch to Win2K and the ctrl-alt-delete is how to get to logoff or shutdown.

mlmcc
0
 
LVL 18

Accepted Solution

by:
mdougan earned 25 total points
ID: 6988872
My experience is that this will likely happen with a database that is over 30mb and/or has more than 20 concurrent users.  I also found that the problem occured much more frequently on one of our servers that was running Novell (I don't know if it had anything to do with Novell, or just that it was a slow server).

I suspect that it happens because of a speed/timing issue.  So, when you have more concurrent users, perhaps an update operation gets thrown out of synch because of the other demands on the DB, or because of network traffic, or because of a slow processor on the server.  

Best advice I can offer is pretty much what everyone else has been recommending, keep the database size down by frequent compacting (and backing up just in case), put the DB on the fastest server you have available, and try to ensure your network is running as quickly as possible.

I'd say that you are pretty much at the limit of what Access is capable of, and so, you should really consider, migrating to SQL Server.
0
 
LVL 10

Expert Comment

by:arana
ID: 6991805
mlmcc:
cause was not clear in my case
but we had a corrupt windows installation
and a very faulty network card.
in my case.

db is now 80 MB and running smooth. (anyhow we are moving to sqlserver or postgress in 2 months)
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 6993171
Hi Guys
Thanz so much for the imput
i am on site now so i can begin to trouble shoot these problems, it is really weird last week in the
space of 2 hrs the one access database file got corrupted 3 times. that was on thursday, now friday,saturday
and monday there have been no problems

i will keep u filled in on the process

thanx again
i really appreciate it

Cheers

Craig
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 10

Expert Comment

by:arana
ID: 6997645
see if any of your pc's is using w95 or an older data engine, try to upgrade to the last engine
specially if you are using DAO
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 7001180
Hi Guys
Well i downlaoded the ldb application - very cool :)
anyway i had a database corruption and i checked the aplication and it said that my connection was suspect and no one elses. So i sat back and thought well hasa corruption happened when i have not been there. Honestly i cannot remember a time when it happened when i was not there.

So i am thinking that is just me when i walk into the office the database file then goe - Lets rev Craig and give him a hard time - :) :)

anyway i think of 2 thigs
1. That i am inside access itself creating querys and checking tables and generally i have access open all the time when i am developing with vb.
or
2. It could be my onboard network card, i had real serious problems with Realtik network cards, they kept dropping the connection accross the network and corrupting the db files. As sson as i replaced all the realtik network cards for 3com cards problem disappeared. So i am maybe thinking it could be my on baird network card


what do u guys think ??

thanx

Have a great weekend

0
 
LVL 14

Expert Comment

by:Matti
ID: 7001725
Hi!

I think you shuld do the development whit replicas, not that user database.

Those Realteck NIC's have bugs but I think these have been updated if your is on MB then it could need a special driver from the MB maker, not had that brand in three years, formal bad and ofthen happended that PnP systems can't found them but in this case it's not on PCI slot.

Matti
0
 
LVL 18

Expert Comment

by:mdougan
ID: 7002589
Yea, the question would be, are other people trying to read from the database while you're in editing stuff in it?  If so, that could easily be the problem.  You should only open it for editing if no one else is connected to it.  Or, as Matti suggests, have a user version, do your editing in a different database, and then overlay the user version or migrate your stuff to it when no one is using it.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7002756
I agree.  We do all our development and testing in a copy only the development team can access.  When we are ready we drop the share on the network drive after giving all users notice and export all changed files (no tables) to the production database.  The only time we have to actually open the production database is if we promote a query because we have to set the permissions correctly.

mlmcc
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 7009341
Hi Guys
Thanx for the help
well i am in the database often to run update querys and make changes on the data, i am putting in a network card and disabling the onbaord SIS network facility.

thanx

Craig
0
 
LVL 10

Expert Comment

by:arana
ID: 7009358
i believe your card will be the problem,
but anyways try not to develop on the production database, it should be no problem, but you never know!
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7016984
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20173773.html
http://www.experts-exchange.com/questions/Q.20247869.html
http://www.experts-exchange.com/questions/Q.20253943.html
http://www.experts-exchange.com/questions/Q.20301367.html
http://www.experts-exchange.com/questions/Q.20296324.html
http://www.experts-exchange.com/questions/Q.20296326.html

To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20270469.html

*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 7021798
Hi guys
ok i have tried to keep out of the database file, i stil have to replace the netwrok card i will keep u upto date, so to Moondancer - how can i split all these points to everyone who has helped me try to isolate the problem here ?

thanx

Craig
0
 
LVL 4

Author Comment

by:CraigLazar
ID: 7074780
Hi Guys
Sorry for leaving this question open, still waiting for Moondancer to split the points here bewteen everyone

thanx again

Bye the Bye databases are handling fine now, i am trying to soend less time in the live databases, adn am running still the same amuont of users, i put in a decent netwrok card on my dev pc. Anyway i have not had a corruption on 3 weeks

Great stuff

0
 
LVL 5

Expert Comment

by:Netminder
ID: 7329524
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now