Database Design for Multi-User Order Entry System

[Guest]

Hi,

Have been developing solutions in MS Access for about 8 yrs now and am
trying to standardise the way I do things. One common issue that I face when
dealing with multi-user systems, such as a sales order entry system is how to
best deal with users' requirements of (a) creating new sales orders and (b)
modifying existing sales orders.

Taking the following simple scenario of a database with 2 tables -
SalesOrderHeaders and SalesOrderDetails in a standard One-To-Many
relationship.

What is the recommended way of designing a form & subform for controlling
dataentry? Should it be bound or unbound. Obviously making it bound is easier
and quicker from a development point of view, but provides FAR LESS
functionality. For example if a user loads an existing sales order which has
4 lines, adds 2 lines and then realises he has made a mistake and wants to
undo the changes he has made.
In the past, I have sometime had 2 temporary tables - TempOrderHeader and
TempOrderDetails - into which either the new order is entered or the existing
order is loaded. The form is bound to these tables. Then, we can add the
option to Update or Cancel changes. Is this the recommended way?

I could go on, but hopeful you understand where I am coming from and can
offer some useful suggestions.

Thanks,

Rael

 

[Guest]

Hi Rael,

As you know, by design Access works best using Bound forms... You CAN make
unbound forms, but you have to code around all the bits and pieces that are
handled nicely by Access if you have them Bound.

Have you considered using the standard bound main form/subform method, but
incoporating some sort of user security levels so that standard users can
enter/update records, but power users can delete items as necessary. Of
course, you might want to prevent this from happening after a certain event
happens, for instance, after the invoice has been paid, or printed, or
whatever other criteria you think may be appropriate.

Hope this helps.

Damian.

 

[Guest]

Thanks Damian,

I agree with you about the benefits of using Bound forms. I think that the
reality is that in order to justify continuing using MS Access (over say,
VB.net) is the speed of development. Unbound forms obviously require
significantly more coding, thereby losing some of the great benefits of
Access.
I have found some documentation online about binding forms to a Recordset.
This obviously works well (and easiliy) where there are no subforms. But
without subforms, it is also easily to undo changes with a "normally" bound
form.

I am hesitant to incorporate user security into my solutions for 2 reasons:
1) it adds a level of complexity (and therefore development time/costs) to
the solutions; and
2) I am led to believe that Microsoft have reviewed and totally rewritten
the security model for Access 2007, so am holding off on this feature in the
current Access 2003.

I think that the most viable commercial option for me right now is to stick
with the model of having TEMP tables into which I load the current order. If
the users clicks the "SAVE" button then it updates the propert tables. If
they click on "CANCEL CHANGES" then it doesn't.

Rael

 

[Guest]

Sounds like as good a plan as any...

If you have control over the deployment environment, then Access is fine.
If you don't (eg: you are building a shrink-wrapped product for sale), I
would consider building an exe in VB.NET or something similar.

Damian.

 

[Roger Carlson]

I think you're right. I've used this solutions a number of times and I've
had great success with it.

In many ways, this emulates a 3-tiered system. This can also be successfully
used with SQL Server or Oracle to take advantage of bound forms without
maintaining an open connection to the server database.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L