Is Access A Corruption-Prone Headache or Not? 5

[Jackvmae]

I'd like to ask this question of all the experienced Access developers...

I have started making an Access 2000 application. I plan to sell it to very small offices in a small niche market. Usually these offices don't have more than 5 computers on a network.

Now I am having second thoughts about embarking on this project, mostly because of all the corruption problems I read about with Access. I have this nightmarish vision of dozens of offices calling and complaining about endless problems. What I am not sure of is if my concerns are valid.

So I'd like to ask you all... is this fear justified? Is Access really that bad, a little bad, or not bad at all? Do you have lots of corruption headaches or other frustrations with your Access apps, or is your life as an Access developer happy and relatively trouble-free?

Thanks for any guidance.

Jack

 

[Edski]

Jack,
As long as the developer/programmer is experienced then I don't see any problems with using Access. Sure it's got limitations but I've always found work arounds. As for selling it to niche markets - I can't comment. Sounds like it needs to be very non-specific, which could create problems. Just my opinion.
Good luck though!
ps. I've been writing Access applications for 10 years and I'm still learning new tricks!

 

[MichaelRed]

Access is, in my opinion, quite sensitive to the developers ability / knowledge in the multiuser environment - even if the number of (concurrent) users is quite small. For experienced developers, (Edski) the issues are generally known and "workarounds" or known approaches can be employed which make it quite stable and suitable for small groups. The largest single issue (in my experience) is with data entry, and in particular the use of "Autonumber". Typical (novice) approaches attempt to use the default (sequential) value, which can cause errors particularly in data entry mode. Closely related items (e.g. editing records) also generate errrors (and corruption) in the multiuser environment.

I also have used Ms. A. for quite a while and have had more employment in Ms. A. from fixing db's created by inexperienced developers than I have from creating original applications.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[JeremyNYC]

I agree fully that an experienced developer can avoid almost all of the corruption problems that are out there with Access. But it IS more prone to corruption that MS Sql Server, PostGre, or Oracle.

On the other hand, though we've gotten much better about not arguing about it, Michael and I have different opinions about the autonumber fields in Access. I have used them in production applications in use by up to 25 people concurrently for years with no collisions.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Jackvmae]

Michael,
Can you explain what the problem is with Autonumber fields and how to avoid them. I use autonumber fields in almost all my tables.

Jack

 

[MichaelRed]

Autonumbers are included in the table where they serve to identify the records. Two users concurrently accessing the 'new record' with autonumber may (eventually will) get the 'record' at he same time. Since it is the 'new' record it is not locked, and both users get the same 'return value' - the same (duplicate) autonumber. When the records are then saved to the db, there is the rule violation for the 'No Duplicates", which is set by Ms. A. (Jet) for the AutoNumber field. In the "best case" scenario, one user gets an error and looses the entered data. In other situations, corruption of the db can (eventually will) occur. If it is just the error, all it will 'cost' is the time lost by the Users in recovering from the confusion, questioning WHAT, and WHY this occurs in addition to the re-entry and your time explaining the issue to the Users and their 'bosses' (your customer or boss). In the worst case, it is a carrear altering (and NOT in the POSITIVE direction!) event.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Gazonice]

Hi,

If it's any help.. I have written several 'off the shelf' applications and sold them to the general public, normally for small office or home office work.

Of the 1000's of copies I have sold, a few support calls have been made, these relate to:

1) Out of date Jet errors - solution: download and install latest Jet service pack and restart computer.

2) 'Table 'TempMSysAccessObjects' already exists.
which I advise clients to call me to talk them through fixing this - 5 minute phone call - (This issue has arisen 3 times)

I would say the reliability rate to sales stands at 98% - which is good.

I made a forum for users to submit errors and issues - this is very underused - another sign that Access is stable and generally reliable.

I think the main tips for a good application is make it watertight from a user point of view, automate as much as possible and make the interfaces friendly and understandable. Finally check it thoroughly and get as many mates as you can to try and break it before you sell it.

I will continue to develop Access applications for the foreseeable future, even alongside .Net applications.

Hope this helps,

Garry

 

[danvlas]

In working environment, corruption usually occurs in very specific conditions:
- computer freezes and needs Ctrl+Alt+Del or 'reset' (most frequent cause)
- power failure (second frequent cause)
Good old solution: backup, backup, backup...

In development environment, various tests, imports, exports, code editing can create some troubles: 'Network Connection has been lost', 'error communicating to ActiveX server' and others.

Solution: compile and backup. When such things happen, get the latest backup and continue.

Although I haven't encountered corruption because of AutoNumber (just like Jeremy), I admit that Michael has a point in this matter and I've started monitoring closely the behaviour of the 'beast'. Unfortunately, no matter what I did, the AutoNumber was generated correctly. Which doesn't mean it can't fail when you least expect it...

In any case, Access applications can be really robust and very fit for small business solutions, if they are built carefully.

My personal opinion is that Access as a 'client' is one of the best products available in the market right now.
I can't say the same about Access as 'server'...

Bottom line, I wouldn't be scared about corruption problems. But train users to back-up their work so that they don't spoil yours...

HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[BNPMike]

Despite what other people have said, Jet has a major weakness compared to more professional dbmss in that it doesn't have journalling. Nothing is protected from failures but with Jet you can't recover. Thus your customers will lose data when this happens. If you are just publishing rail timetables it doesn;t matter but if it's selling bonds on the international capital markets, you might find your company going bankrupt fairly quickly.

 

[Jackvmae]

Regarding the Autonumber field...
(For Michael, Daniel and Jeremy)

I never thought of the autonumber field as a source of problems because it's never caused any at our office (that I know of). Also, in my app almost all new records are first filled in by the user on an unbound form, thus minimizing recordlock time. Then DAO is used to update the new record to the table. It's at that point that Access generates the new autonumber.

The chance of two people clicking their respective "Update" buttons at the identical moment is remote. Still, just to be on the safe side, I guess I should insert code to first check if the recordset is locked and, if not, then temporarily lock the recordset while the update is in progress.

You all sound like you have much more experience than me, so I ask you: am I on the right track in that thinking? Do you think that would eliminate this potential problem with autonumber conflicts? I don't know because I have not progressed to the point where I have any experience locking recordsets in code.

Thanks,
Jack

 

[BNPMike]

Any database is prone to clashes. Locking-based ones will show the record unavailable to the user. Change-Verfiy ones will tell the user their update has a problem. Either way the user is inconvenienced but that's all. I'm not aware that Jet is any more prone to conflicts like this than any other well-known package.

Autonumber is a tricky concept. Just make sure you're not relying on predictable numbers. The key thing is they control themselves which sounds like a good thing but if you need to control them, then you've got the potential for heartache.

 

[danvlas]

Jack, if you lock the recordset pessimistically and use error trapping to re-start the procedure until the update is successful, I believe you're home free...

But make sure the loop is not infinite. Maybe have it run 10 times and if it still fails, give a message to the user to let you know immediately and see what the trouble really is. 99% probability that it's your mistake, not user's.

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[MichaelRed]

Not trying to be obstinate, ... (the inevitable BUTT) ... The "new" record (or tenative record) in Ms. A, is not part of the recordset, so all the locking available doesn't help!

That is the point. You CAN'T lock it. This applies to any/all fields or the recordset, so the only way to relliably get any PK value for MS. A. is to hold the value(s) in a seperate recordset (table) which you CAN lock. While I haven't seen it recently, I have seen / read where MS. recommends NOT using AutoNumber in some situations (in addition to replica dbs).





MichaelRed
m.red@att.net

Searching for employment in all the wrong places