 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / March 2005back end corruption
I have a system with an Access 200 front end and Access 200 back end. there are approximately25 users on the system at any one time. When I occasionally (every 4 - 6 months or so) compact the back end, for the next week or so, I have to repair the back end on a daily basis. I will get a report from one user that the back end is corrupted. All other users can continue without error until they log off and try to log back on again. Any ideas as to why this may be happening, or how to prevent it? Nik, To begin with, a 4-6 month period between compactions sounds waaaaay too long! I would have thought of weekly as the absolute minimum (I do nightly). That said, there's gotta be a reason for that behaviour, and I suspect that is rubbish left-overs in either the FE / BE; the best way to clean them up is to produce a fresh, clean copy, by importing everything into a new, blank database (no, compacting and repairing won't clean those off). I'd do that on both ends, and re-distribute the new, clean FE to the users. I suppose you have given each user their own separate copy of the FE, haven't you? If not, this is also a possible reason for what you're facing. HTH, Nikos > I have a system with an Access 200 front end and Access 200 back end. there > are approximately25 users on the system at any one time. When I occasionally [quoted text clipped - 6 lines] > > Any ideas as to why this may be happening, or how to prevent it? Yes, Each user has their own FE. The 4 -6 six month period came about because of the problems when compacting on a weekly basis. I have tried re-creating the BE, but still get the same problem. I usually recreate the BE each time I need to repair it (depending on time available). I haven't tried to rebuild/compact the FE at the same time. I'll attempt that next time. Thanks > Nik, > [quoted text clipped - 22 lines] > > > > Any ideas as to why this may be happening, or how to prevent it? Any code left in the BE? Did you debug / compile it? > Yes, Each user has their own FE. > [quoted text clipped - 35 lines] >>> >>>Any ideas as to why this may be happening, or how to prevent it? The BE contains tables only. The last 'fix' (Monday), I also did a compact/repair and a re-compile on the FE. The system has corrupted again today. > Any code left in the BE? Did you debug / compile it? > [quoted text clipped - 37 lines] > >>> > >>>Any ideas as to why this may be happening, or how to prevent it? >The BE contains tables only. >The last 'fix' (Monday), I also did a compact/repair and a re-compile on the >FE. > >The system has corrupted again today. This is an interesting problem. One thing to try would be to import all the objects in the BE into a new MDB. Doing a compact/repair on the MDB leave minor corruption in the MDB. Other than that I'm not sure what to suggest given how it corrupts after you do compact and repair. For more information on corruption including possible causes, determining the offending PC, retrieving your data, links, official MS KB articles and a list of vendors who state they can fix corruption see the Microsoft Access Corruption FAQ at http://www.granite.ab.ca/access/corruptmdbs.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Some recomandations that can help : - First review your network and server (disconnects and hardware problems) - Keep the BE DB Size < 150 Mb, if not is feasible, split the data in several mdbs - Use ADO instead of ODBC - Keep connection to the DB only as needed, and only one - Do not use a permanent conn to a dummy table when start the application, this increase the performance but increase the risk of damage when the app crashs - Use W2000 o WNT to store the BE, never use W98 or ME - Be sure that you have the last service pack of the Jet Database Engine This links explains some of this issues (from Microsoft) : Information : http://support.microsoft.com/default.aspx?scid=kb;en-us;306204 Fix damage : http://support.microsoft.com/kb/209137 Avoid damage : http://support.microsoft.com/kb/300216 Choosing Db : http://support.microsoft.com/default.aspx?scid=kb;en-us;168549 (Old but helps) Taken from the last URL (Choosing.doc) : "For example, with proper tuning, the current version of Microsoft Access can certainly handle 50 concurrent users and 100 megabytes of data" Hope that it can helps JP > >The BE contains tables only. > >The last 'fix' (Monday), I also did a compact/repair and a re-compile on the [quoted text clipped - 24 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm > Some recomandations that can help : You probably want to respond to Nik directly rather than to me. > - First review your network and server (disconnects and hardware problems) Pretty generic comment. > - Keep the BE DB Size < 150 Mb, if not is feasible, split the data in >several mdbs Why does the size of the BE DB matter? > - Use ADO instead of ODBC How would you use ODBC to a Jet BE? What about DAO? > - Keep connection to the DB only as needed, and only one > - Do not use a permanent conn to a dummy table when start the application, >this > increase the performance but increase the risk of damage when the app >crashs Really? I respectfully disagree. I can't see how a permanent connection to a table or database would increase the risk of damage. It's not like you're actually updating anything. And for better performance this is required. > - Use W2000 o WNT to store the BE, never use W98 or ME Agreed. > - Be sure that you have the last service pack of the Jet Database Engine Agreed. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm > The last 'fix' (Monday), I also did a compact/repair and a re-compile > on the FE. This surprises me. Did you do a re-link as well as the compact and re-compile? Compacting the BE can lead to 'file corruption' if you have made changes to the BE design without updating the FE: I normally just compact the FE: many people re-link, if you are having problems, you should do both. This is a problem associated with design changes to the BE, so theoretically it could also happen after a repair of the BE, but only if the BE already had undetected damage. (david) > The BE contains tables only. > The last 'fix' (Monday), I also did a compact/repair and a re-compile on [quoted text clipped - 56 lines] >> >>> >> >>>Any ideas as to why this may be happening, or how to prevent it? Nikos, Tony & David Thanks for the input. To summarize :- After a compact of the back end (and compact/recompile & re-link of the front end), I get corruption on a daily basis for about 7 to 10 days after. When fixing the corruption, I copy the back end locally and rename the copy on the server (paranoia on by part). I repair the back end. I create a blank database and import all the tables from the baclk end (there are only tables in the back end). I compact the new database, rename to as the back end and put in onto the server. I sometimes (but not always, depending on time constraints) repair/compact the front end and re-link to the back end. The last compact was done late on Feb 24th. we have now been error free for 3 days, but each working day from Feb 25th to Mar 8th I have had to repair the back end. If all goes accordingly, we should remain error free until the next compact (in about 4 - 6 months time) Again thanks to all. > > The last 'fix' (Monday), I also did a compact/repair and a re-compile > > on the FE. [quoted text clipped - 72 lines] > >> >>> > >> >>>Any ideas as to why this may be happening, or how to prevent it? >To summarize :- >After a compact of the back end (and compact/recompile & re-link of the >front end), I get corruption on a daily basis for about 7 to 10 days after. This is still very weird. And makes no sense at all. Not that I don't believe you because I do. Your postings are quite detailed and very credible. It is possible that mismatched Jet versions on different systems are causing this problem. That is Jet 4.0 SP8 may be on all systems but one which is still running SP7. What I've done is use the various API calls available and am checking the version number and date/time of a crucial dll, msjetxx.dll, to ensure it matches what I have on my system. See the Verify Appropriate Jet Service Pack is installed page at my website for more details including sample code: www.granite.ab.ca\access\verifyjetsp.htm But what is really puzzling is that compact and repair does a compact as part of the repair process. Thus if it is the compact that is causing the problem you should continue to be getting the problem. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm One of my colleagues suggested that the problem may be an indexing issue. A user hits an invalid index and corrupts the back end. The repair/compact then fixes the 'broken' index. I am not sure how likely this is because if the first compact/repair causes problems then surely the other compact/repairs will do the same thing. > >To summarize :- > >After a compact of the back end (and compact/recompile & re-link of the [quoted text clipped - 26 lines] > Microsoft Access Links, Hints, Tips & Accounting Systems at > http://www.granite.ab.ca/accsmstr.htm >One of my colleagues suggested that the problem may be an indexing issue. A >user hits an invalid index and corrupts the back end. The repair/compact then >fixes the 'broken' index. I am not sure how likely this is because if the >first compact/repair causes problems then surely the other compact/repairs >will do the same thing. Yes, indexes can get corrupt. That's the cause of the "Search key not found" (or similar) message. But it still doesn't make any sense why corruptions would occur for a few days or a week and then stop for several months until the next time you do a compact & repair. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.