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