|
Home > Archive > MS Access Multiuser > April 2005 > shared DB for the first time.
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
shared DB for the first time.
|
|
| Jarryd 2005-04-13, 7:24 am |
| 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
| |
| Nikos Yannacopoulos 2005-04-13, 7:24 am |
| 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
Jarryd wrote:
> 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 2005-04-13, 11:24 am |
| 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.
"Nikos Yannacopoulos" < nyannacoREMOVETHISBI
T@in.gr> wrote in message
news:eAVOKaBQFHA.3928@TK2MSFTNGP09.phx.gbl...[color=darkred]
> 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
>
> Jarryd wrote:
| |
| Nikos Yannacopoulos 2005-04-14, 3:25 am |
| Jarryd,
Pls see my comments in between your post.
HTH,
Nikos
Jarryd wrote:
> 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.
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.
>
> TIA,
>
> Jarryd.
>
>
> "Nikos Yannacopoulos" < nyannacoREMOVETHISBI
T@in.gr> wrote in message
> news:eAVOKaBQFHA.3928@TK2MSFTNGP09.phx.gbl...
>
>
>
>
| |
| Tony Toews 2005-04-21, 11:24 am |
| Nikos Yannacopoulos < nyannacoREMOVETHISBI
T@in.gr> wrote:
>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!
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
| |
| Tony Toews 2005-04-21, 11:24 am |
| "Jarryd" < Jarryd@youllneverkno
w.com> wrote:
>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.
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
|
|
|
|
|