Data Management

10 ways to prevent Access database corruption

Nothing frustrates Access users like a corrupted database. With a little know-how, you might get lucky enough to repair the database or at least recover the data. Sometimes, a third-party product that specializes in data recovery can help. But you could end up rebuilding and re-entering data. (Or rather, your replacement could.) A more proactive approach is to avoid corruption in the first place. Here are some strategies for preventing your databases from becoming corrupted.
By Susan Sales Harkins and Gustav Brock

Nothing frustrates the full spectrum of Access users -- from casual user to developer -- quite like a corrupted database. With a little know-how, you might get lucky enough to repair the database or at least recover the data. Sometimes, a third-party product that specializes in recovering data from a corrupted database can help. But you could end up rebuilding and re-entering data. (Or rather, your replacement will rebuild and re-enter it.)

A more proactive approach to the situation is to avoid corruption in the first place. Here are some strategies for preventing your databases from becoming corrupted.

Note: This information is also available as a PDF download.

#1: Split your database

If more than one person will access the database, split it into two pieces: a backend that stores the data in tables and a front end that contains everything else (forms and reports). Access has a wizard that holds your hand through the process so there's no excuse not to split a shared database. Name the two ends appropriately. Remember, the backend contains your "gold" -- your data. With the data and interface objects in separate databases, you can easily replace the front end from a backup, if necessary. In addition, this setup makes enhancements easier to incorporate into your system.

There is some discussion about whether the front end should be stored on a network server (and shared) or on a local drive. This decision really has no impact on corruption, so the choice is yours. If possible, consider write-protecting a shared front end. If the worst happens and something destroys the front end, simply obtain a fresh copy of it to replace the broken one and reboot the local system.

#2: Store temporary tables in a backend

If a database creates, populates, and then deletes temporary tables, keep those tables in a separate backend database to prevent bloat. Name the additional backend accordingly, making its purpose obvious. This additional backend can be shared or local.

#3: Don't use memo fields

Avoid using memo fields if possible. They often, indirectly, cause corruption. Even though the database in these cases often can be repaired, some content in the memo fields might be lost. If you need memo fields, keep them in separate tables and create a one-to-one relationship to the parent table(s). Even better, move memo tables to a separate backend database file and name the file accordingly, to indicate its purpose.

#4: Don't store picture files

Usually, you shouldn't store picture files in a database. If you must, treat them the same way you would a memo field (see #3). Access has no problem attaching tables from multiple backend databases.

#5: Create temporary tables to speed up queries

If you run complex or nested queries (where one query pulls data from others that hit still others), Access may write a lot of temporary data that you never see. Most often, this happens when a query that works with a small amount of data performs slowly, putting stress on the JET engine. If Access chokes during this process, you can end up with a corrupt backend file.

To prevent this problem, write some of the temporary data to temporary tables. There's no universal method to recommend. Analyze the specifics and run some tests to find the best solution. However, sometimes the use of just one temporary table can minimize the chance of corruption and speed up the queries by a factor of 10 or more.

#6: Be careful with wireless networks (WiFi)

A connection may work fine, but multiple users or powerful neighborhood networks (or other noise sources) can abruptly cut off the connection. That can corrupt the database file if you are writing to it at the time. This type of interference isn't a problem if users are mostly reading from the database.

#7: Be careful with WAN connections

A WAN connection that covers any connection from a local system to a server via the Internet can cause trouble. Reading the database may be slow but acceptable. However, writing to the database is error prone and can cause corruption. When bottlenecked traffic interrupts data transfer, Access times out, believing the connection has been lost. This behavior usually leaves the backend database in a corrupted state.

#8: Don't put Mac and Windows users on the same network

If Mac and Windows users share the same network and experience problems, establish a separate network for database users. Macs are extremely noisy, and typical Mac applications generate vast amounts of network traffic when moving large graphics files and printing. In a shared environment, use only high-quality network components.

It's best to keep the database traffic separate from the graphics traffic, as much as possible. You can accomplish this by allowing administrative workstations to connect directly to the server with the shared database via a local switch.

#9: Troubleshoot network hardware

If corruption just happens from time to time, you may have to deal with a network hardware issue. First, try to narrow down the workstation and swap the error-prone station with another. If the issue follows the workstation, you know that's the source. It might be easiest to get rid of the workstation.

If the problem isn't specific to the workstation, the error is most likely to be found in the connection from the workplace to the network switch -- including the actual switch port. If the cable's in good shape, swap the ports between the workstations. If the error source now swaps too, replace the switch; if not replace the cable. If cables aren't permanently installed or don't cross from floor to floor, you can try replacing them before swapping workstations.

#10: Check the server's configuration

Sometimes, the configuration for the server where you've stored the shared backend database file is the culprit. You'll need a specialist to track down and resolve this type of problem. There are several possibilities, from server parameters to a malfunctioning disc controller to a misconfigured RAID array.

You can temporarily move the backend file to a different location, like one of the popular NAS devices or a shared folder on a workstation. If the problem with corruption goes away, call the specialist. If not, the corruption's source is somewhere else (#1 through #9).


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the publications director for Database Advisors. You can reach her at ssharkins@gmail.com.

Gustav Brock is a consultant and developer at Cactus Data ApS, an independent software house specializing in accounting and time billing systems and custom database applications. He has been working with Microsoft Access since version 1.0 and is a frequent contributor to the forum of developers hosted by Database Advisors.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

91 comments
robertsmike
robertsmike

Corruption in access databases is a common problem and may arise due to many reasons like virus attack, database header file corruption, hardware issue etc. Whatever be the reason of the corruption, all data become inaccessible to users. Found some cases and their solution to fix the corruption in access database files: http://accessdatabaserepairsoftware.blogspot.com/


Hope it will be useful !

Awahili Guni
Awahili Guni

Actually the author is correct but should had put a little more details or depth in aspect as why the two and two shouldn't be together. In a way yes they can and in another way no they cannot. Just like one cannot run on a PCI with IDE and RAID together (one has to have one or the other) or it will corrupt itself. With MAC - there is a clash when IDE is use and will create it to become noisy; not so much when RAID is used. This would pertain to those who are running on older Servers or various BIOS (yes, there are some "bad BIOS" out there) in spite of all the configurations one has done. Is there a work-around for this? So far I have run-in to many forums all over, seen many so-called "answers" but the responses from others, I would have to imply that the majority of them were complains over those who responded that it worked. So which is better IDE or RAID? It actually really depends on your BIOS and how your Server and Computer (both Win and Mac) are functioning; not so much as the Access Program itself. In some cases, while it is unfortunate, there are no resolutions and something has to give - so in a way, I do partially side by that statement of MACS being noisy; but in another way, there should had been a little more in depth or detail explanation as why in my humble opinion. While I am solely Windows, not anti-MAC at all - but I have come across such issues of this sort. Might that be the answer to your question?

david.chapman
david.chapman

If you have more than ten screens in your application a sure way to cause corruption is to copy and paste a screen. You might think it worked, saved you a lot of time and is great to obtain a uniform feel, but next time you you start the database that screen will not work, unexpected results will occur and it will get worse and worse. Be very wary of temporary tables in a backend of a shared database. More than one user might create the same table. I prefer them on the user's PC with the application, have never blamed them for corruption.

jw
jw

As a power user, I was responsible for two Access databases. One suffered corruption problems, the other I devised a web (asp) front end. It never got corrupted (an unexpected bonus) - I suspect because it only ever saw one user i.e. the web server on the same box. Somebody more techie might comment whether my theory is right?

tony
tony

In a prior life, a reliable access app suddenly started corrupting weekly. It turned out that new computers had been purchased, and they were set to go to sleep after a period of time. Having a shared MS Access application open when the PC goes to sleep is a recipe for disaster, if the data is contained in the Access DB. - Tony

tony
tony

I develop in several environments, and find that I can quickly develop a nice looking front end in MS Access. What I have learned (due to the corruption issue) is to not store the DATA in MS Access - I create a SQL db for the tables, and link them to MS Access (possibly with some temp tables in access as needed). Sometimes I need to develop pass-thru queries to force the query processing to occur in SQL rather than in access. That way, if the access "database" (really application) becomes corrupt, you can restore an earlier copy without data loss. And, I've not had the application version get corrupted since I started using this methodology. There are several free SQL servers that can be used (MS SQL Express, MySQL) as well as the big dog SQL servers. - Tony

lrnstevens
lrnstevens

And when all that fails you can always turn to specialized data recovery software. I use AccessFix and I'm very happy with the results. You can check it out at www.cimaware.com/main/products/accessfix.php

ssharkins
ssharkins

I had to start a new thread--that previous one wouldn't take any new responses, so I hope you see this. This is also a second post in response, but the other one has never shown up. I don't know whether I sent it into a black hole or just goofed and didn't publish it at all. I like a good healthy discussion, even when folks disagree. (Okay, I really want to be right all the time... .) Seriously, I learn from these conversations. As long as we're treating each other with respect, I'm fine with disagreements. Like you, I agree that this is an open forum and discussions often take on a life of their own. Having said that, I know that many "big dogs" have little (no) respect for Access or developers who use it -- not all of the big dogs, but some of them. We've seen some of that in response to the article. I think it's out of place in a professional discussion. I am not talking to you personally dawgit -- just in general. But, I've got thick skin and it is an open forum. Anyone who wants to trash Access should do so, just don't expect those of us who use it to say, "Oh... isn't that sweet..." ;) BTW, I have a fan club? Who knew? Oh... more importantly, who's collecting the dues???? ;)

cactus
cactus

Fact is that Access stands behind thousands and thousands of applications "out there" - from tiny wipped-during-lunch-break apps to extensive mission critical ERP apps. Thus, to claim that Access is not a real database only proves lack of knowledge. These apps have to be maintained and nursed, and if you know how, it is not more difficult than so many other tasks taken care of by an IT pro. Again, claiming that it is not possible or reliable to run a shared Access database on a normal well-tuned network, only proves lack of skills and knowledge. Further, to compare a shared database as Access (or rather JET) to a standard SQL server engine makes no sense. Two different animals. In many cases a server based engine is indeed preferable but simple things as backup, server resources or portability may be prohibitive and in many cases pure overkill. This is an interesting discussion on its own but is beyond the scope of this article. /gustav

cactus
cactus

As you may know, to write such 10 statements in limited space, some corners have to be cut. But the remarks on the noisy Macs are based on experience. We have several advertising agencies and TV/film production companies among our clients all of them running an Access based time/billing/ERP application, and the list of "issues" we have seen is endless. Main problem is that Mac tech people (not users, they don't know anything) believe they understand networking. As a general rule and warning (exceptions exist of course) they don't. Worse, when (not if) something misbehaves regarding the network, they blame _everything else_ connected to the network for being the source of trouble. One example. Recently at a client we saw a corrupted backend once a week. Normally it was perhaps once a year. Nothing at our end was changed so the cause had to be something else. The network was one of those that had been expanded in 57 steps; it was close to impossible to track down why things were set up as they were. Local switches were all over and users in one end printed to a big RIP and printer across the network. So we cut through all this, isolated the pc users on a separate LAN with just an uplink to the mothership network to gain Internet access. From day one: No corruption. Now the client moved. A brand new network was installed and guess what: Corruption again. We found out that the network was not configured as to our recommendations, and again to cut short, we isolated the pc users and their server and printer on their own switch and trouble went away. The reason for the trouble with the Macs is not the Macs by themselves but how the users punish these machines. Typically they run 24 hours all week long loaded with all kind of applications that download all sorts of data. Users run heavy application that saves and copy multi gigabyte files between workstations, servers, RIPs, local FTP servers etc. etc. Our favourite is when they attach 100 MB files to e-mails wondering why this brings their Exchange to a crawl. "The mail is down!" they claim when the e-mail client times out, but the answer is "Wait and see." It is possible to make Macs and their users to coexist with pc users and Access but it requires a top-notch network which Mac people typically neither do or will understand not to say are willing to allocate the money for. It is more fun to spend money on a 30" flat screen or a new stack of RAID disks for a NAS. /gustav

Tony Hopkinson
Tony Hopkinson

Doesn't matter what OS you store it on, they are designed to manipulate whole files, not bits of them. The best you can do is block one from writing to the entire file and then tell the blocked process the state the file is in, is now, was in. Just because a text file in on a file server, doesn't make notepad a client server program, so why should sticking the MDB on a common Mac,, a common windows, OS2, even and NFS directory on a VAX make access client server.? Location of the file has nothing to do with it, it's concurrent managed access to the content that makes client server.

cactus
cactus

Servicing a web front end is a quiet life for an Access database (one user). If the database file is located on the same machine, no network issues either. Under these conditions it is nearly impossible for the database to get corrupt. Still, when shared among several users, corruption is the exception. Regard the advice given in the article as safety precautions. Or, should you experience corruption issues, use them as guide for the first things to check for. /gustav

joseph.x.dowski
joseph.x.dowski

I currently work as an analyst for a Healthcare company in CT. USA. but I've also worked in the same capacity in both the banking & financial services industries as well. My primary tool for reporting and analysis is Access. In my experience I find that Access is typically used to plug holes and/or shortcomings of the reporting and analysis capabilities of the larger systems it gets its data from. It's powerful, flexible, and already on most desktops...like any other app it can be used poorly too but IT based solutions to departmental level problems are typically slow and expensive so this is where Access fills a need. I've made a living creating/maintaining/using apps in this niche and I don't see anything changing this dynamic anytime soon....other than more Access apps moving to the web....

mabingle
mabingle

@cactus  Same here. I have used it to build apps that we didn't have and needed, or that management wouldn't pay for. One example was an app to control a $37 million dollar budget for IT. When I was given the position of Director of IT, it was almost budget time. We didn't have a system to control the budget or collaborate with all the other departments we supported. So, I created a multi-user Access application to use. It took me 2 weeks (on my own time) to create it, install it, and train everyone using it. The mounds of paper on my desk was reduced significantly, and the time to do the budget was reduced by half. Finance loved the app, as did everyone that used it because it took a lot of stress out of budgeting time.


So, Access is powerful and fast to develop. It can get complex if you let it, and some of the VBA code I have seen appears to be overkill at times. I think some people just want to impress others needlessly.


Access has been very good to me in a lot of ways and I support it 110%.... and I'm an IT guy. --- Mike

dawgit
dawgit

That your address book is a real library. It's a tool for MS Office, that's all. That's what it is designed for. A person can fly to China in Pipercub, but I would rather take a 747 myself. -d

bob.zormeir
bob.zormeir

It is possible to pound nails with a wrench... it's just not a good idea. If you're running an extensive mission critical ERP app in Acess, you're probably in need of a somewhat bigger toolbox. And Access, JET and SQL Server are actually three different animals.

Tony Hopkinson
Tony Hopkinson

are scalability and the fact that it is the tool of choice for those without a lot (any in many cases) database knowledge. When you have what amounts to power users choosing the backend for enterprise level systems, and then presenting the resultant mess to a 'professional', any phobias are easily explained. Access as a front end for designing and executing applications, that you have to look at on a case by case basis. Using application logic to get round the fact that it simply doesn't have the functionality of a full DBMS generally means you should not be using it as a backend in the first place. A lot of the time access isn't chosen as a technology because it's an optimal or even practical solution, but because the people with the task have no other skills that could apply. Picking out another tool is just going to give them a different set of failures, probably more expensive ones as well. It's also seen as 'cheap', given it's already present if you have a full office solution. A problematic valuation at best. Access is a real database, as an xml file can be, a collection of csv files, or even a filing cabinet. As for your well tuned network / server comment, aside from the skills deficit I alluded to earlier, you are having a laugh. On that basis I could make the same claim for storing the data in a csv file. Of course it can be done, should it though?

ssharkins
ssharkins

Glad you brought that up. :) Gustav and I know a developer who has a very successful ERP program, built in Access, on the global market.

cactus
cactus

To quote yourself: > Access is not client server, It wasn't in 96, it isn't now. It can, however, share an mdb/accdb database file on a fileserver and this is what this article is about. Access can also use a database file exclusively, or it can connect to most database engines via ADO or ODBC as the client in a client/server setup, but that is a different story. /gustav

cactus
cactus

While Susan and I appreciate feedback, would you please stay On Topic for the benefice of all. /gustav

Ken Mahy
Ken Mahy

Anybody who has undertaken a large carpentry or construction project knows that having the right (high quality) tool for each task saves a huge amount of time and frustration - and leads to a far better end result. This article might better have been entitled "10 Warning Signs That You Shouldn't Be Using MS Access"!

dwutka
dwutka

The real problem is that this thread is talking about three worlds, the lightweight database world, the heavyweight database world and the interface world. Access is a tool that can play in all three fields, but isn't always the best tool in any of them. You sound like you are in the Heavyweight database world Tony. You have the typical views of someone stuck in that one particular environment. It's like a trucker going down the highway, annoyed at the drivers weaving through traffic in their Mazdas. Your 18 wheeler would crush a Mazda in a collision, but what annoys you is that you'd have to catch it first. There are people that work in all three worlds. I'm one of them. I work in the interface world (mainly web based now). I work in the lightweight world (which Access actually dominates in), and I work in the heavyweight world (both Oracle and SQL Server). Access' true shortcomings in the heavy weight world are the following: No triggers. Size limitation. No transaction logs/recovery No Full Text searching No server side processing (

cactus
cactus

Good points Tony. However, the topic really is not why or why not to use Access (or rather JET as a JET backend can be attached from, say, a .Net or Visual Basic application as well). To compare Access/JET with a server based SQL engine doesn't make much sense - something like comparing a bicycle and a car. There is a purpose for both techniques which can be proved by the MS release of SQL Server 2005 Compact Edition which is a single file based database very similar to JET but with the syntax of SQL Server. Several more or less qualified opinions have been posted here earlier. I can add one more: that the JET drivers will probably not be published in 64 bit versions while this is the plan for SQL Server 2005 Compact Edition. /gustav

cactus
cactus

That is true. Thanks for the clarification. /gustav

Tony Hopkinson
Tony Hopkinson

I just wanted to make it clear to the less knowledgeable that putting your mdb on a non-windows OS won't address most of access' corruption issues. Obviously exclusive access or using a full client server back end will.

Tony Hopkinson
Tony Hopkinson

it's crap then? .. :D I agree with everthing except the last bit, most people who use access don't know what normalisation is. To be fair, as a power user tool analysis, normalisation could get in the way.....

dawgit
dawgit

You pretty much sum it up, and rather nicely at that. -d

bob.zormeir
bob.zormeir

I see Microsoft Access being appropriate only if: 1. You don't need to comply with HIPAA, and even if you don't need to comply, you're not storing any personally identifiable information. 2. You don't need to comply with SOX, and even if you don't need to comply, the database needs no controls surrounding data integrity and auditability. 3. The data in the database has no impact on your contractual obligations to other companies. 4. The business function the database supports is not critical to your organization, so if the database was unavailable tomorrow, your organization would not be impacted in any material financial or business sense. 5. The cost of losing the data entry since your previous backup (day, week, other interval) is minimal, and you have a process in place to accomplish the re-keying. 6. The database has no need for security, because all of the data maintained in the database could be openly shared in public with no material impact to your organization's financial or business well-being, including losses due to negative PR if somebody copies the whole thing to an iPod and walks out the door. 7. You are storing a very low volume of data. 8. The database is being used by a limited number of users 9. The database is being maintained by one or two developers. 10. The database is trying to solve a simple problem that will not significantly increase in scope. And, most importantly, you have no reason to expect that any of these conditions will change, because Access doesn't scale, can't be made inherently secure, and can't be made robust in the sense that any major database is robust. It also doesn't scale well for development by multiple devs, is only somewhat object oriented, is nearly impossible to use in a TDD environment, and seems to inspire creative denormalization in an attempt to overcome its architectural shortcomings.

Tony Hopkinson
Tony Hopkinson

hand over their 'systems' to some knobhead IT type, and then it goes to ratsh*t. I'm sorry, I have to go in a delete any duff records as part of the hourly housekeeping routine. ????? !!&&***) The macro is located in cells K1, H345, and LJ22, I think?!?*! I have this tee-shirt

Tony Hopkinson
Tony Hopkinson

a lotus 123 wordprocessor. :p I suppose we are all guilty of reaching for the tool we know best, it's just some of us have more of them lying about.

dawgit
dawgit

...just how many people out there in the business world (IT?) think that way now. We have to get used to that, it's now the normal talk. :( -d

dawgit
dawgit

It's not you. And I hope you haven't taken anything I said as meant for you, especially you personally. I just don't work that way. You're an Access Developer, I expect that you would be an advocate. But I also feel that here in a public forum on should expect a little cross-fire and criticism. It's just normal, and it's how most people learn and grow. I think that you should have seen that most, or at least myself, havan't trashed Access itself, I just don't think it's the answer for everthing. (I don't think you do either.) But, with that being said, I don't have much use for others, less tolerant, who think any negetive word towards MS is Heresy. They circle those wagons, and commence to snipe at any opposed to their chosen doctrine. (Did you know you had a fan club? :) ) Does that clear anything up at all? -d

ssharkins
ssharkins

I am reminded of something I just read last week -- from another Access colleague. During a meeting with a new client, the guy running the show called Excel a database. :)

ssharkins
ssharkins

I'm not sure who you're replying to, but I can share my own perspective, fwiw... As an Access developer, I get soundly trashed on a regular basis by people who consider themselves the "big dogs..." Now, the truth is, I don't really mind it because... my work is easy and I'm doing what I want. I'm happy. But, I admit, I do take disrespectful comments personally. For instance, someone earlier in this conversation went so far as to say developers using Access weren't professionals -- that's just bs. On this subject, I really wish that those of you who trash Access and those of us that use it, could see some of the applications that I see and actually sit down with these developers and talk, without the insults and disrespect. I think you (collective, not you personally) would come away from the meeting feeling differently.

Tony Hopkinson
Tony Hopkinson

I'm sure access would have a much better professional reputation, if the decision to use it was more often a technical one. I'm as sure there are situations where you could not recomend or even countenance using it, as I am where I would recomend it. There will be some grey areas at the edges where your greater experience in access would suggest to you it was a possibility, where I would discount it as an option, and possibly vice versa. However that's you and I, people with vague inkling of the fundamentals. Who decides? probably not someone in IT. Hence your post...... There can be good reasons both technical and pragmatic to go with access, you can't get away from the fact that as soon as you choose access as a repository for data, it cannot be critical. If it was the first time the mdb got corrupted, it would be switched out.

dawgit
dawgit

Hopefully, or at least ideally, it should be decided by the actual needs of the company. Unfortunatly software applications are too often chosen useing the lastest, greatest, marketing offers. When we finally get to see through the clouds (fog) of the marketing hype, then we can realy get to choose. Since Access is only a part of the MS Office world, and many companys (at least in Europe) are moving more to an Open Source model, the Questions associated with Access are becomming mute. -d

ssharkins
ssharkins

Who decides what the right tool is for any job?

dawgit
dawgit

I'm not sure I'd really want to know. Why is it, still, if if there is any criticisms on anything MS, the Wagons are circled, and the ?We's" and "x and I's" want to start shooting? Personally I thought you had expressed yourself rather well above. But, it hasn't seemed to quite penetrate the wagons. It seems the FUD is creeping up on us here. -d

dawgit
dawgit

How can you handle such complex intangible concepts found in modern programing? Ok, I'll keep it simple, (?On Topic??) ?the Right tool for the Right Job?. As I said, Access has a purpose, to be used, as designed, as a container for MS Office Work. End. Anything else is normally better handled by some other method. Discussions leading otherwise, as pointed out by many others above, are counter-productive. IMHO -d

Tony Hopkinson
Tony Hopkinson

A bad connection does not time the client out, no response within a specified interval does that. Data is only lost if you are the sort of skilless numpty who throws it away before you know it was saved. What you can guarantee with a client server DBMS short of an extraordinarily unlikely series of events, is if says it's done it's done. If it doesn't, it isn't. Would I consider a BMW a bad car because of a good driver,NO. Would you consider a bycycle a good car because of a good driver? You really don't get it do you MS jet is not doing what a server does, it's doing without a server. Client Server 101 Grab hold of ten of your colleagues, go to the vending machine, now every body get a coffee at once. Access tries to be a bit more civilised about it, want's you to queue up in an orderly fashion. It can't enforce it though. However if you let big Phil drive the vending machine, all you little p1ssants are gonna wait until he's ready to serve you. That might sound unfair, but you either have a coffee and are down the price, or you still have your money. With access's attempt at the process, some one ends up with coffee spilled over them, some one else is ten cents to the good, there is a lot of change under the vending machine, now it's broke, and Big Phil is looming over you. In theory the operating system could take the place of big Phil, but it was designed to manage entire files, not eight bytes some where inside one. HtHs

dwutka
dwutka

Hmmm, let's see...first the client breaks up the data stream into TCP/IP packets, and starts sending them out the NIC through the twisted pair Cat-5 cables, which usually go through a series of hubs (basically uncontrolled intersections) or switches (controlled intersections (preferable), until they reach their destination, which is determined by IP Addresses (which on a switch is really using MAC addresses). The subnet range on the client machine is used to determine if the packets have to go to the gateway (router). When the packets reach the server, they are reassembled. Now, all sorts of things can go wrong there. The TTL may have expired if the traffic was delayed, at which point those packets are requested again, they may have been corrupted, bad physical connections, em interference (do you know how many electricians think that Cat-5 cable insulate power lines really well? It's shocking) The difference between a client/server setup (such as an Access FE with a SQL Server Backend) and in a Client Side setup, is that in the c/s setup, SQL statements are sent to the Server, and the work is done on the server. Resulting data (or results) are sent back to the client. (Most of the time, Access, as a FE, can pull more info then is needed and so some work on it's own, it does this when it needs to do things that it can't send through as a SQL statement, such as performing VBA functions with WHERE statements....). In the Client Side setup, Access (or Jet) is reading parts of the .mdb file (in a way, it is doing what a server side db does, to it's own files). If the backend is local, this is done without the network, if it's not, then it is giving file requests to the server. A bad network connection in a Client Server setup, is just going to time the user out. In a Client side setup, if the database is flagged as locked, it could leave that flag, leaving the database in a 'corrupted' state. My point was, that the data lost is still lost in either situation. If my SQL statement was putting new data into a table, or altering data in a record, if the network connection fails, the .mdb will corrupt, and the server side will create a failed transaction log. Either way, the network is the one at fault. Don't blame the user problems on the tool. If most bad drivers drove BMW's, would you consider the BMW a bad car?

bob.zormeir
bob.zormeir

And the best part? I've seen a network with so many Access databases that they were competing with each other for bandwidth. They were literally corrupting each other. Meanwhile, SQL server was chugging along without missing a beat, doing more work, and generating less network traffic. According to Wikipedia: "Microsoft Access can be applied to small projects but scales poorly to larger projects involving multiple concurrent users because it is a desktop application, not a true client-server database." According to the Microsoft KB: "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." So Microsoft appears to be agreeing with Wikipedia, and (coincidentally) with me. It's a desktop application. Yes, I know you can put it on a network where several people can get data from it. That doesn't change its basic architecture. And if all your apps work well on your network except the one (Access) that?s not designed for it, how is that the fault of the network?

Tony Hopkinson
Tony Hopkinson

Huh ? You mean one that's busy? You can't mean one that occasionally packets go astray can you? You do know what client server architecture is don't you??? It's not access on a workstation and an mdb file on something called ServerOne..... Access is not client server, It wasn't in 96, it isn't now. This is why you can get much more out it, running behind a web or custom built server. Now try not to prove me right about tool of choice for the unskilled OK. Which it is, it was designed to be power users tool, and it's pretty good at that in 99.99% of scenarios.

dwutka
dwutka

'can't rely on your data getting from front to back' LOL. That's the irony with your posts Tony. Access does NOT corrupt itself. It only corrupts when there is a BAD network. I hate to point out the obvious, but a bad network will prevent a server side DB from getting the data too. The only difference is that it will log the failed transaction and move on. But your data didn't get there still. There's nothing 'magical' about a server side db that is going to be able to retrieve data from a client through a network having problems. As for the 'tool of choice for the unskilled', got a chuckle out of that too. The tool doesn't determine the skill. A poorly built system is a poorly built system no matter what platform is used. Finally, you mention that your experience with Access stopped in 1996....sorry. That explains a LOT about your prejudice towards access. Access 95 was the Titanic of databases, though the Titanic worked better. 97, on the other hand, was the complete opposite.

Tony Hopkinson
Tony Hopkinson

Erm, well in a nutshell, wrong. SQL Mobile definitely isn't heavy, mysql isn't heavy, Interbase, Firebird..... Triggers, stored procs, table functions, SSIS, DTS, blah blah, clustering, terabytes of data, text search, all utterly useless if you can't rely on your data getting from front to back and vice versa. That's where access falls down badly, that's why the article was required. Bugger all to do with heavy, everything to do with data integrity. There is absolutely no conceptual difference between sharing an mdb file between various 'clients' or an xml file. Tune for one, you've tuned for the other. If I miffed you, 'with tool of choice for the unskilled', it is. I didn't say you were unskilled, yet. While most of my recent experience is on the full dbms side, recent for me is from 1996, the previous nine years was paradox and access.....

Tony Hopkinson
Tony Hopkinson

it's client server, which is where access falls on it's butt. I was replying / expanding on your points, those in the original thread, I'm not particularly qualified to comment on any more, not compared to those whoe use access on a regular basis anyway. I did split access as a front and back end as well, I look forward to the day when it makes that distinction as well. I do lightweight solutions, but without volume and user level integrity, access is a non starter for 99% of the systems I work on.

Editor's Picks