 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / April 2005shared DB for the first time.
Hi, I have a DB that I created to place orders (who hasn't?!). I had two different types of orders that had to have their own numbering system stored in the same table. So the field for order number would incur duplicates, but was made unique by the corresponding entry in a second field specifiying which type of order it was, i.e. A/1111 is different to B/1111 (A and B stored in the same field and 1111 and 1111 stored in the same field). I got the DB to generate the next number using if statements and recordsets with a little help from this newgroup. Works a treat! I have been asked to share the db so that others (two more users) can add orders. I have placed the DB on a network share, without splitting it or making an MDE or converting it to an ADP. Will I have any problems with the recordsets autonumbering, or anything else for that matter, if people are adding records to the same table at the same time? TIA, Jarryd Jarryd, To begin with the splitting is a must in order to avoid corruptions, and you must do that even if for no other reason. Use the built-in splitting wizard (Tools > Database Utilities > Database Splitter), it's a piece of cake; just make sure you place the back end in its permanent network folder, so you don't need to change links later. After the split, give each user their own copy of the front end (preferably stored locally on their PCs) to work with. Now, the issue of avoiding PK conflicts: the trick is to use an unbound form for the data entry, which will allow you to calculate the PK value at the time of storing, as opposed to using a bound form with the calculation in the default value property of a bound control (or two, in your case), which calculates the PK at the time of entering a new record. In the unbound form scenario, the chance of two users saving a new record at the exact same split second it takes for the code to run is next to nothing; on the contrary, under the other scenario, if it takes a user 20 seconds to enter a new record, the chances of another user starting to create a new record in between are anything but negligible; if instead of 20sec it's more like 60sec or 90sec, you will get conflicts all the time! HTH, Nikos > Hi, > [quoted text clipped - 16 lines] > > Jarryd Hi Nikos, That all sounds like good advice. The reason I haven't split the DB is because it is still a "work in progress". So I figured that if I left it as is then I could work on both ends of the DB (front/back) quite easily - obviously not while users are using it! And then when I had finished it I could split it up and distribute the front end to them, leaving the back end on the server. But perhaps that convenience will cause me some inconvenience in the future so it would be better that I split it now. As for the unbound form, I reckon that sounds fantastic... but how?! I have already done so much work on the orders form, to recreate all the controls and all the code will take weeks with all the other things I have to do. I am still trying to imagine how it would work anyway. So what you are saying is that you can create a form that will create the new record at the click of a button, for example, once all the controls have been populated with data? And create some kind of pop-up if you the user closes the form without first clicking the button? Can you think of any easy way to copy all that code and all those fields across. Plenty of the controls are unbound and are populated with if statements are DLOOKUP so I might be exagerating the problem, but from the outset it does seem like a huge job. Please let me know what you think. TIA, Jarryd. > Jarryd, > [quoted text clipped - 42 lines] >> >> Jarryd Jarryd, Pls see my comments in between your post. HTH, Nikos > Hi Nikos, > [quoted text clipped - 5 lines] > on the server. But perhaps that convenience will cause me some > inconvenience in the future so it would be better that I split it now. You can stick with the monolithic mdb for the development, as long as you make sure you are the only user. Splitting right before delivering to users is fine. On the other hand, splitting early on and putting the BE on the server (ideally in a folder only you can assess for the time being!) has the additional merit of giving you an idea of the performance early on, so you know if extra considerations are required in the interest of performance. > As for the unbound form, I reckon that sounds fantastic... but how?! I have > already done so much work on the orders form, to recreate all the controls > and all the code will take weeks with all the other things I have to do. You do not need to recreate the controls, just clear their controlsource property, and then the form's recordsource property. > I am still trying to imagine how it would work anyway. So what you are saying > is that you can create a form that will create the new record at the click > of a button, for example, once all the controls have been populated with > data? Exactly. You can use either a recordset operation or an append query (SQL expression and execution thereof in code). The query method is probably easier for the header/main form where you only have one record, while the recordset operation is preferable (performance-wise) for a multi-record subform (line items). > And create some kind of pop-up if you the user closes the form > without first clicking the button? Exactly. > Can you think of any easy way to copy > all that code and all those fields across. Start by making a copy of the original form to work on. > Plenty of the controls are > unbound and are populated with if statements are DLOOKUP so I might be > exagerating the problem, but from the outset it does seem like a huge job. I believe you are indeed exaggerating this... anyway, in my humble opinion it's worth a shot. > Please let me know what you think. > [quoted text clipped - 48 lines] >>> >>>Jarryd >That all sounds like good advice. The reason I haven't split the DB is >because it is still a "work in progress". So I figured that if I left it as [quoted text clipped - 3 lines] >on the server. But perhaps that convenience will cause me some >inconvenience in the future so it would be better that I split it now. Working on the backend MDB simply means that you open it when required. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm >Now, the issue of avoiding PK conflicts: the trick is to use an unbound >form for the data entry, which will allow you to calculate the PK value [quoted text clipped - 8 lines] >negligible; if instead of 20sec it's more like 60sec or 90sec, you will >get conflicts all the time! Alternatively in the fields before update event check to see if both fields have been entered. If so save the record. The downside is that required fields can't be allowed on tables. OTOH if you were to save the current value of the A and B key in two separate records in a table that'd work too. I will do a lot of work, relatively speaking, to avoid using an unbound form. Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.