[AccessD] Help need input fast - A2K / SQL2K Client Server App (Bound or unbound form)

Bob Smith flyonthewall2003a at yahoo.com
Wed Feb 12 20:48:00 CST 2003


Hi there I am new to the list. I hope someone here can
provide some help and input from personal development
experiences. 

I need to develop a client server application but I am
trying to determine which way to go with the physical
design.

Here are the systems details:

Database will be SQL2K.
Server has a CAL (Client Access Licenses) level of 35.
All workstations are Win2K with Access 2000 RT minimum
installed.
(I would like to develop the FE in Access 2000. Access
is a very powerful tool when used correctly. Because
Access is generally more accessible than say VB it
often gets a bad reputation from bad experiences
related to inexperienced developers and their first
projects.)


Here is the data scenario:

The application follows one of the more typical data
relationships. There is an orders table (I will call
T01 here) containing a few specifics about the order.
There is a related table (I will call T02 here) that
contains the related order details. There is a
one-to-many relationship between T01 and T02. Actually
there will almost always be 1 record in T01 for every
75 records in T02 (exceptions of course). 

99.5% of the time no two users will view the same
order. No one will edit the same order simultaneously.
So when a user is entering an order and the 75 related
order details there is 0% chance that anyone else will
need to edit that information.

This will be fairly busy. There will be about 25
concurrent data entry personnel all entering orders at
once four about 8 hours straight. 

What I need help with:

I have two designs in mind but I am not sure which to
use.

Design 1:
Create the FE using an MDB with two LOCAL tables to
temporarily store the order and its details the users
is entering. The input form and sub form will be bound
to the local tables. Once the user has completed their
work and is ready to "post" the information to the
database they click a button on the form. Some code
will fire off checking a few things and then make an
ADO connection to the database and then fire off a
stored procedure passing the local data (bundled in a
transaction that can be rolled back if it errors out)
to the database. After the tasks completes
successfully the local table would be cleared of all
data. This seems to be a common logical design, work
local and push when completed. 

What are the Pros and Cons of this design?

Design 2:

ADP bound form with sub form. I thought about creating
a form bound to T01 but set the form for data entry
and having a sub form bound to T02 also set for data
entry. Since SQL uses true record level locking, and
since no one will ever be editing the same orders, my
understanding is that even though 25 people are
keying, since no one is working on the same records
there should be next to nothing in the manner of
record locking issues?

Is this accurate? If so can you cite from personal
experinces? What are the Pros and Cons of this design?


Additional questions:

Can anyone give me a clear explanation as to why one
or the other is more or less desirable? 

Is there a time when I should use one or the other?

I am very concerned about record locking. I want to
minimize this as much as possible. The end-users using
this application, and those they report to, are
non-technical and this needs to stay very user
friendly. For that reason Design 1 seems to be the way
to go. But if record locking is handled as I outlined
in Design 2 then Design 2 would be the fast way to go.
Any comments about record locking?

How would a report bound to the data affect record
locking in Design 1 & 2?

I need as much details as possible and if you have
personal experiences you could offer up I would
immensely appreciate it. I really need explanations as
to why 1 or 2 is better and in which situations. 

Thank you for your assistance and have a nice day!!!

Bob





__________________________________________________
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com



More information about the AccessD mailing list