Bob Smith
flyonthewall2003a at yahoo.com
Thu Feb 13 14:06:00 CST 2003
Hi Everyone, I really need some help with this. I posted this question last night but I think in my rush to ask for help, I worded my question poorly. I know on the surface this looks lengthy but I hope someone will read through it and reply back. I tried to reword sections I now realize were not as clear as they could have been. If any of you are experienced with Client Server development using MS Access 2000 and can afford to take some time to read this, please do. I’m really worried about this project. I’ve never done something so update intensive before. Basically at the core of my question is record locking. I would like to use an Access 2000 ADP as the front end of my SQL 2000 database for a project that will be high volume data entry. They will only use the application to enter new records. So if I bind a form (set for Data Entry) to a table will I encounter record locking errors even if no one will be adding/editing the same records? I am afraid that if I go the ADP bound form that I will have issues even though no one will be working on the same records. Does an Access 2000 ADP use page locking with SQL like Access 2000 mdb does with Jet (is the record locking a function of the database system SQL2K/A2KJet or of Access)? Can anyone comment on this? Below are the details of my plight if you want to read what I am looking at and what I am considering. Thank you for any assistance or input you can provide. Bob. Details: I need to put together an application that will track orders and their details. This will be a SQL 2000 backend with a Server CAL of 35. The workstations will all be Windows 2000 workstations with Access 2000 RT minimum installed. At the core of the application will be two tables. T01 houses the order specifics and T02 houses the order details. There is a one-to-many relationship between T01 and T02. Almost always there will be 1 record in T01 for every 75 records in T02. There will be 25 concurrent data entry personnel entering new orders for about 8 hours straight. No one will edit the same order simultaneously. Actually no one except the supervisor will even look at it again through the interface (and even then only 1 or 2 orders out of thousands per day). There will be a handful of reports that will be used to view the information. Run once at the end of the day (possibly once in the middle while they are still working). As a user enters an order and the 75 related order details there is 0% chance that anyone else will edit that order. What I need help with is coming up with the design. I have two in mind but I am not sure which to use. I am hoping someone can tell me which would be the appropriate one to use. Design 1 (FE - mdb with two LOCAL tables): Two local tables will hold the information for the current order and detail information. After current order has been entered the operator will click a button that will fire off code to pass that data to the SQL2K database on the server. After posting the current order to the server database all data within the local tables will be purged. So as they start on the next order that order will be the only order contained within the local tables. (I had considered storing the current orders details within an array instead of using local tables but if I go this route storing the information within the tables would allow the operator to shut off the computer if they needed and then come back and pickup where they left off.) 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): Bound form to T01 set for data entry. Bound sub form to T02 set for data entry. This would be faster and easier to create but I am worried about record locking. Does Access use page locking with SQL like it does with Jet (is the record locking a function of the database system SQL2K/A2KJet or of Access)? If it’s the database system 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 no record locking issues? What are the Pros and Cons of this design? Can anyone give me a clear explanation as to why one design or the other is more or less desirable? Design two would be fast but will I hit a lot of record locking issues even though no one will be working on the same records? Thank you for any assistance or input you can provide. Bob. __________________________________________________ Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day http://shopping.yahoo.com