Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    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.

  2. #2
    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

  3. #3
    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

  4. #4
    Join Date
    Sep 2004
    Posts
    10
    Peter,

    I did this already. Thanks for the suggestion.

  5. #5
    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.

  6. #6
    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?

  7. #7
    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.

  8. #8
    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.

  9. #9
    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 ...

  10. #10
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  12. #12
    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.

  13. #13
    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!?

  14. #14
    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.

  15. #15
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •