If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Access DB Keeps Getting Corrupted

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Question Access DB Keeps Getting Corrupted

My database keeps getting corrupted -- beyond repair -- and I don't know why. I have been using Access for many years and never had a problem like this.

The simplest task may cause Access to freeze, and it usually stays frozen for several minutes. Sometimes it snaps out of it; other times it prompts me to repair it. It is often beyond repair and I have to replace it with a back up. Corruption occurs at least once a week, sometimes twice.

The database is small: 20 tables, ~100 total objects, 4MB. This U.S. DB does, however, reside on a server in Germany. One table is linked to an external DB, which resides in the same location. There are about 10 users across the U.S., but usually no more than 3 are in the DB at once.

Any help with this would be gladly appreciated. Thank you.
Reply With Quote
  #2 (permalink)  
Old
Cavalier King Charles
 
Join Date: Dec 2002
Location: Préverenges, Switzerland
Posts: 3,739
http://support.microsoft.com/default...en-us;303519#8 says:

" Microsoft Jet is a file-sharing database system. A file-sharing database is one in which all the processing of the file takes place at the client. When a file-sharing database, such as Microsoft Jet, is used in a multiuser environment, multiple client processes are using file read, write, and locking operations on the same shared file across a network. If, for any reason, a process cannot be completed, the file can be left in an incomplete or a corrupted state. Two examples of when a process may not be completed is when a client is terminated unexpectedly or when a network connection to a server is dropped.

Microsoft Jet is not intended to be used with high-stress, high-concurrency, 24x7 server applications, such as Web, commerce, transactional, and messaging servers. For these type of applications, the best solution is to switch to a true client/server-based database system such as Microsoft Data Engine (MSDE) or Microsoft SQL Server. "


izy
__________________
currently using SS 2008R2
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 64
Hi, im not really experenced enough to handle your situation, but I would suggest that you go to the debug menu in the code area of an object, click on "compile your database Name" and see if it shows up errors in the coding

cheers

Peter
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Peter,

I did this already. Thanks for the suggestion.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
That's just it -- it's not a highly used DB, concurrent users are rare, the DB is small. I created similar DBs too many times to count. I have never experienced this problem. Moving to SQL Server is out of the question. Any other suggestions? Thanks.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 32
Use mySQL as the back end (tables) and try that. If it doesn't corrupt then your problem is with Access. I would agree that given the number of users it shouldn't corrupt so easily.

Have you reconstructed the database from scratch? It might just be that the old one is damaged beyond (effective) repair?
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Derrick,

I don't know anything about mySQL, so I am not ready to consider that yet.

I have created a new DB, imported the objects, then compacted/repaired. I tried this a few times. There has been no improvement.

However, the most important tables are now linked from another DB. When the database becomes corrupt, the data does not. Then I replace the DB with no data loss. It's a step up, but does not solve the problem.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2004
Posts: 32
You don't have two or more users sharing the same front end do you? That's a recipe for disaster.

I have people sharing a database across several sites n different towns, but each user has her own copy of the database to access (they all link to a back end database with the tables). the one time I accidentally set two users up to look at the same front end the database corrupted before the end of the morning!

By the way MySQL is easy to uyse as long as you know SQL, it's free for persoanl use (although I'm not too sure about a commercial licence). I'd honestly suggest downloading a copy and getting some practice in using it. If you Access database gets too difficult to run across a network, just build the tables in mySQL, use Access as the fron end and the only difference your users will see is an increase in speed (I'm not totally biased I use Access a lot and it's a great RDBMS, but it does have some limits.)

Last edited by DerrickM; 01-14-05 at 10:02.
Reply With Quote
  #9 (permalink)  
Old
Grand Poobah
 
Join Date: Sep 2003
Location: MI
Posts: 3,713
Quote:
Originally Posted by vbagirl
My database keeps getting corrupted -- beyond repair -- and I don't know why. I have been using Access for many years and never had a problem like this.

The simplest task may cause Access to freeze, and it usually stays frozen for several minutes. Sometimes it snaps out of it; other times it prompts me to repair it. It is often beyond repair and I have to replace it with a back up. Corruption occurs at least once a week, sometimes twice.

The database is small: 20 tables, ~100 total objects, 4MB. This U.S. DB does, however, reside on a server in Germany. One table is linked to an external DB, which resides in the same location. There are about 10 users across the U.S., but usually no more than 3 are in the DB at once.

Any help with this would be gladly appreciated. Thank you.
Is the DB password protected? What kind of network? Speed? How is your DB setup? Do you have a FE BE setup (client/server) or do all your forms/reports and tables reside in same DB?

Another method to repair: Try creating a DSN (thru the admin tools-> ODBC Data Sources->System Tab) and attempt the repair there ... It can repair DB's that the MS front end tools cannot ...
__________________
Back to Access ... ADO is not the way to go for speed ...
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Derrick,

Yes, the users are using the same front end. I will be splitting the database this morning and instructing the users accordingly. (It makes sense to start with this before I consider your other suggested fix.)

I have never had this problem before. In similar apps, I have used Access 2.0, 97, or 2000. This time I am using Access XP, which suggests to me this is a newer issue.

Thanks for your help. I'll let you know how it goes.
Reply With Quote
  #11 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,523
MS Access it not an effective Enterprise Solution. Period. And you are trying to use it internationally? You have a choice of paying for a database server or paying for all the administrative/maintenance hours and lost productivity of your current solution. If you choose option B, then live with the consequences.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2004
Posts: 10
Update: I have split the database and gave each user his own frontend. However, the twist here is that all the frontends reside on the server with the frontend. Local frontends simply would not run with a remote backend. It's a little weird, but it works.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Apr 2002
Location: Portugal
Posts: 146
Hi,

Do you have all the users accessing to the server and opening there the applications (front-ends)!?

If the solution of remote access to the back-end didn't work, and opening directly the front-ends from the server did, well i'm surprised with that, but i could guess the result of it. Your server or your applications will have sudden crashes, because even with lots of memory, let's say 8 Gbytes, the server can't handle 10 users or three at the same time working directly in a program. What happens if the server is backing-up the information!? and the other tasks that a server does (mail managing, other databases managing, etc..)!!? this all counts, because certain tasks are priority in the server, note that a server wasn't made for the clients work directly in it, but to serve the clients with information shared.

blindman is right, Ms Access is not an enterprise solution, but in a certain enviroments it works, but internationally...

Why don't you try to upsize the back-end and link the tables via ODBC!?
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
Join Date: May 2004
Location: New York State
Posts: 1,177
If you don't use proper Record Locking procedures, MS Access will happily lock up on you every time data is saved in a multi-user environment.

If you don't use these procedures, the best thing to do is disallow more than one user at a time to access data files.

Create a table with two fields; UserName and Section - both string. UserName can be obtained using the ENVIRON("USERNAME") function, and Section can be obtained by adding the appropriate code to your menu forms.

When user1 logs in, have the table record that fact. When he then opens any data object (a table, or a form that uses a table), check the Section field in the table. If ANY Section is currently in use, gently tell him that he can't use the database now, and to kindly try again later. The reason is so the "live" user won't lose his data by user1 logging in and saving other data. When user1 goes out of the form or table, his Section field gets erased; when he logs out, his record in the table gets deleted altogether.

Of course, if NO section of the database is in use, user1 can do whatever he wants, and the Section field is filled out with the Section he's using. Ergo, when user2 attempts to enter any section of the DB, he will be locked out.

The table only needs one record for each person logged in, although only ONE person may have his Section field populated.
Reply With Quote
  #15 (permalink)  
Old
Moderator
 
Join Date: Dec 2004
Location: Madison, WI
Posts: 3,925
Problem with corruption

I'd disagree with the assumption that MSAccess is not a good Enterprise solution. If coded correctly, it works great as an Enterprise solution. Although it has its quirks (what programming tool doesn't), it can be utilized to match against any programming tool on the market.

Your problem with the database always corrupting could be the cause of many things, some of which are not even MSAccess related but most likely it has something to do with the code in Access. Do you have different version of Access on the same machine? Importing forms/reports from one database to another and not immediately running the debug/compact and repair before you start changing the code will almost always corrupt the database if you have 2 versions of Access on the same machine.

Bound forms are usually difficult to work with when using Access in a multi-user environment. Try using unbound forms and the coding techniques taught in school (i.e. separate functions to write/retrieve/update customers.) It's kind of like object-oriented programming but it keeps records from locking when multiple users get into the same tables.

Although we use SQL Server as a backend and write/retrieve hundreds of thousands of records, I've written lots of programs with Access tables and unbound forms. The problems I experienced with the database corrupting were usually code based/incorrect syntax/not closing recordsets properly, etc..

Sorry I couldn't be more help. I just don't want you to get discouraged that Access isn't the correct solution. You can spend a lot of time learning C++ and re-write your code but it all depends on how you write the code and utilize the tool your using.

I would recommend though that you use MySQL or SQL Server. This will save you some headaches and does have it's advantages. But there's no reason you should give up on Access.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On