Ensuring Data Conservation in Multi-User Environment

[Steve R.]

I have not read everything in the whole thread, but I have to say I am concerned about some of the advice you are getting.

eg. SteveR keeps advising against the use of Access for the back end, and against bound forms.

I have to say that this is ridiculous. A wholly access system with bound forms will perform perfectly (and I mean 100% perfectly) adequately on this system.

There are very few reasons not to use bound forms, and using unbound forms as a general case is unlikely to be efficient.

I recognize that your knowledge exceeds mine. And thanks for the help that you have provided me over the years.

My comments are based on very limited experience. (My database development was incidental to my regular job.) In that limited experience, I had issues with "dirty" data and lost records which were resolved through the use of unbound forms and the use of a non-Access back-end.

The suggestion to use a non-Access database actually came from a post I made on this forum many years ago. I was not able to locate it. So I don't recall who made it. But you will note that there have been posts saying that Access has had some problems working over a network. I even noted the positive reviews of one thread on the use of Access as a back-end. Surprisingly this positive thread still contained the observation: "The corruption problems stopped when we started using SQL Server Express as our backend database. I think that speaks for itself. – Robert Harvey".

Another reason for suggesting a non-Access back-end database. They are free open source databases. Very suitable for small organizations. No licensing fees to Microsoft.

You may have a legitimate point that I ended-up with an "inefficient" design. Is there a better approach? (rhetorical)

Well we will never know. Once the data integrity and performance problems were resolved, I did not pursue any alternative approaches that may have been more in compliance with what you suggest. Furthermore, I have since retired. (No frantic phone calls, as of yet, that the database has "crashed".)

 

[jdraw]

Just to note that I agree with Dave and spike. Changing the backend to Oracle, SQL Server, MySql etc is not the answer. Debugging and reviewing the database structure compared with the requirement would certainly be first steps. If conditions and finances warrant/allow a move then SQL Server Express or MySql are free and available.

There is more info on transaction processing here, and you can find more via Google/Bing.

As has been stated, the default constructs of Access -- bound forms, form/subform etc - have been used by many sites in many configurations without issue. The critical part is to get the tables and relationships designed to support your business requirements. And that can be an evolving , but necessary process. Rarely is the preliminary design sufficient for operations. Testing scenarios, data model adjustment, test data for both "good" and "bad" data are a great starting point.

I find it hard to believe that moving to unbound forms solved whatever issues the poster may have had. There are times for unbound forms, but certainly those are far more limited than bound forms.

 

[Steve R.]

Since posting, I realize that "one of those clarifications" is in order.

My recommendation concerning the use of unbound forms only applies when a new record is generated as a result of data entry. Based on many of the comments above, the use of an unbound is not considered beneficial. So my approach could be considered going "against the current".

All forms used to edit existing records are bound forms.

My apologies if there was any confusion concerning that issue.

 

[gemma-the-husky]

generally speaking, preventing incomplete records when entering data can easily be achieved in bound forms by a combination of data validation (generally with the form's beforeupdate event, and the use of field settings "required" and appropriate relational integrity)

The idea is for the system to refuse to accept a record until it is known to be valid - it therefore is another part of the system design process.