|
 |
access thread: Users Save at the Same Time
Message #1 by "enZo :-\)" <enzaux@g...> on Tue, 27 Aug 2002 17:59:24 +0800
|
|
Hi!! This is somehow a continuation of the "Primary Key" saga. I have this
sample data for clearer view of my problem.
TranID BranchID TranDate TranNo
--------------------------------------
1 01 JAN 01 2002 00001 ---> 02-0001
2 01 JAN 02 2002 00002 ---> 02-0002
3 01 JAN 01 2003 00001 ---> 03-0001
4 01 JAN 02 2003 00002 ---> 03-0002
My Transaction Number consists of three fields in the table, BranchID, TranDate and TranNo,
so the result would look like for ex. CWA-02-0001. I have follwed ur suggestion to use
another field as my primary key of the table. So I've created another field (TranID)
as my primary key of the table. Yearly TranNo resets to 0001 so next year there would be
a transaction number CWA-03-0001.
TranNo is created programatically (findmax-addone method). My problem is when two users
save at the same time, both users get the same TranNo (eg both will have CWA-02-0002).
I know what part goes wrong, it's in the creation of the next TranNo (findmax-addone)
because when they save at the same time they tend to get the same max number and add one
the same time giving them the same result. If TranNo is my primary key there would be
no problem because I can put error handler on primary key error but unfortunately TranNo
is not my primary key.
Any suggestions on how would I make way for this problem?
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #2 by "Richard Gibson" <rgibson@w...> on Tue, 27 Aug 2002 11:02:20 +0100
|
|
If tranno was your primary key you would get an error and you would be able
to handle it............
it's not the fact that it's the primary key that would produce the error but
the fact that, being a primary key, the field has its "duplicates OK" option
set to No.
I guess that simply setting that value for your transno field should give
you the same error when two people are trying to update as it would if it
were the primary key.
Give it a try anyway.
Regards
Richard Gibson
IT Manager
The information in this message is confidential and intended for the
addressee only. If you have received this message in error please delete
and notify the sender, any other action may be unlawful.
The views expressed in this message are personal and not necessarily those
of Walkerpack Ltd unless explicitly stated.
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: 27 August 2002 10:59
To: Access
Subject: [access] Users Save at the Same Time
Hi!! This is somehow a continuation of the "Primary Key" saga. I have
this
sample data for clearer view of my problem.
TranID BranchID TranDate TranNo
--------------------------------------
1 01 JAN 01 2002 00001 ---> 02-0001
2 01 JAN 02 2002 00002 ---> 02-0002
3 01 JAN 01 2003 00001 ---> 03-0001
4 01 JAN 02 2003 00002 ---> 03-0002
My Transaction Number consists of three fields in the table, BranchID,
TranDate and TranNo,
so the result would look like for ex. CWA-02-0001. I have follwed ur
suggestion to use
another field as my primary key of the table. So I've created another field
(TranID)
as my primary key of the table. Yearly TranNo resets to 0001 so next year
there would be
a transaction number CWA-03-0001.
TranNo is created programatically (findmax-addone method). My problem is
when two users
save at the same time, both users get the same TranNo (eg both will have
CWA-02-0002).
I know what part goes wrong, it's in the creation of the next TranNo
(findmax-addone)
because when they save at the same time they tend to get the same max number
and add one
the same time giving them the same result. If TranNo is my primary key
there would be
no problem because I can put error handler on primary key error but
unfortunately TranNo
is not my primary key.
Any suggestions on how would I make way for this problem?
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #3 by "enZo :-\)" <enzaux@g...> on Wed, 28 Aug 2002 10:21:17 +0800
|
|
Actually what I was thinking is lock the table so that no one could save from the
table except me. But my problem is how can I know programmatically that the table is lock
or is used by another user? So that I can have a code that will triggerts to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #4 by "Haslett, Andrew" <andrew.haslett@i...> on Wed, 28 Aug 2002 13:39:13 +0930
|
|
Enzo, again, PLEASE DO NOT CROSS POST!
You keep sending messages to two groups at once, even though you may be
replying to a message that was only posted to one group.
If you wish to post to two groups, please do it separately, remembering the
context that the initial or previous post was made.
Cheers,
Andrew
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Wednesday, 28 August 2002 11:51 AM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #5 by "enZo :-\)" <enzaux@g...> on Wed, 28 Aug 2002 14:24:06 +0800
|
|
Sorry
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Wednesday, August 28, 2002 12:09 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Enzo, again, PLEASE DO NOT CROSS POST!
You keep sending messages to two groups at once, even though you may be
replying to a message that was only posted to one group.
If you wish to post to two groups, please do it separately, remembering the
context that the initial or previous post was made.
Cheers,
Andrew
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Wednesday, 28 August 2002 11:51 AM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #6 by "Carnley, Dave" <dcarnley@a...> on Wed, 28 Aug 2002 09:50:24 -0500
|
|
Maybe you can try this, it has worked for me before.
Create a new table with 2 columns : YEAR and NextTranNo. it will store the
next TranNo for use in that particular year, so you would have one row per
year. You could populate it now for 2003, 2004 etc with a "1" for each
year, you see?
So when a user's process needs transaction number, it locks this table. if
this table is locked when it tries to lock it, it retries until it gets a
lock (error handler, resume). Once it has a lock, it reads the value, then
updates it to value + 1. then it releases its lock, and goes ahead to
generate its number and do whatever it needs to do.
The delays it might encounter re-trying to get a lock will be very small
because any single client only has it locked long enough to read it, add one
to it, and write it back out immediately, so this will be minimal. There is
no chance that a dupe will occur, my experience is that no matter how small
that chance is eventually it will happen, and Murphy's law says it will
happen at a most inconvenient time.
dim r as dao.recordset
dim strSQL as string
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = currentdb.OpenRecordset(strSQL, , dbDenyRead + dbDenyWrite) '
creates pessimistic lock
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update ' lock released
r.close
set r = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Tuesday, August 27, 2002 9:21 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #7 by "enZo :-\)" <enzaux@g...> on Fri, 30 Aug 2002 09:31:55 +0800
|
|
Dave,
Thanks!!! Actually right now I'm finding the ADO counterpart of your code
most especially "dbDenyRead" and locking the entire table. I do understand
that CursorTypeEnum do lock but not the entire table but only the record that
is being access. Do you have any idea what to use on this scenario? I'm using
ADO.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 28, 2002 10:50 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Maybe you can try this, it has worked for me before.
Create a new table with 2 columns : YEAR and NextTranNo. it will store the
next TranNo for use in that particular year, so you would have one row per
year. You could populate it now for 2003, 2004 etc with a "1" for each
year, you see?
So when a user's process needs transaction number, it locks this table. if
this table is locked when it tries to lock it, it retries until it gets a
lock (error handler, resume). Once it has a lock, it reads the value, then
updates it to value + 1. then it releases its lock, and goes ahead to
generate its number and do whatever it needs to do.
The delays it might encounter re-trying to get a lock will be very small
because any single client only has it locked long enough to read it, add one
to it, and write it back out immediately, so this will be minimal. There is
no chance that a dupe will occur, my experience is that no matter how small
that chance is eventually it will happen, and Murphy's law says it will
happen at a most inconvenient time.
dim r as dao.recordset
dim strSQL as string
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = currentdb.OpenRecordset(strSQL, , dbDenyRead + dbDenyWrite) '
creates pessimistic lock
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update ' lock released
r.close
set r = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Tuesday, August 27, 2002 9:21 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #8 by "Carnley, Dave" <dcarnley@a...> on Fri, 30 Aug 2002 09:27:28 -0500
|
|
That's good I prefer ADO too. Very similar...
Dim cnnDB As ADODB.Connection
Dim r as ADODB.recordset
Set cnnDB = New ADODB.Connection
' Specify Microsoft Jet 4.0 Provider and then open the
' database specified in the strDBPath variable.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath ' your database
End With
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = new ADODB.recordset
r.open Source:=strSQL, ActiveConnection:=cnnDB, CursorType:=adOpenKeySet, _
LockType:=adLockPessimistic, Options:=adCmdText
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update
r.close ' lock released
set r = nothing
cnnDB.close
set cnnDB = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Thursday, August 29, 2002 8:32 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Dave,
Thanks!!! Actually right now I'm finding the ADO counterpart of
your code
most especially "dbDenyRead" and locking the entire table. I do understand
that CursorTypeEnum do lock but not the entire table but only the record
that
is being access. Do you have any idea what to use on this scenario? I'm
using
ADO.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 28, 2002 10:50 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Maybe you can try this, it has worked for me before.
Create a new table with 2 columns : YEAR and NextTranNo. it will store the
next TranNo for use in that particular year, so you would have one row per
year. You could populate it now for 2003, 2004 etc with a "1" for each
year, you see?
So when a user's process needs transaction number, it locks this table. if
this table is locked when it tries to lock it, it retries until it gets a
lock (error handler, resume). Once it has a lock, it reads the value, then
updates it to value + 1. then it releases its lock, and goes ahead to
generate its number and do whatever it needs to do.
The delays it might encounter re-trying to get a lock will be very small
because any single client only has it locked long enough to read it, add one
to it, and write it back out immediately, so this will be minimal. There is
no chance that a dupe will occur, my experience is that no matter how small
that chance is eventually it will happen, and Murphy's law says it will
happen at a most inconvenient time.
dim r as dao.recordset
dim strSQL as string
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = currentdb.OpenRecordset(strSQL, , dbDenyRead + dbDenyWrite) '
creates pessimistic lock
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update ' lock released
r.close
set r = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Tuesday, August 27, 2002 9:21 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Message #9 by "enZo :-\)" <enzaux@g...> on Sat, 31 Aug 2002 10:40:40 +0800
|
|
Thanks again. Any ideas how to lock the entire table? If you have only an idea.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, August 30, 2002 10:27 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
That's good I prefer ADO too. Very similar...
Dim cnnDB As ADODB.Connection
Dim r as ADODB.recordset
Set cnnDB = New ADODB.Connection
' Specify Microsoft Jet 4.0 Provider and then open the
' database specified in the strDBPath variable.
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strDBPath ' your database
End With
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = new ADODB.recordset
r.open Source:=strSQL, ActiveConnection:=cnnDB, CursorType:=adOpenKeySet, _
LockType:=adLockPessimistic, Options:=adCmdText
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update
r.close ' lock released
set r = nothing
cnnDB.close
set cnnDB = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Thursday, August 29, 2002 8:32 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Dave,
Thanks!!! Actually right now I'm finding the ADO counterpart of
your code
most especially "dbDenyRead" and locking the entire table. I do understand
that CursorTypeEnum do lock but not the entire table but only the record
that
is being access. Do you have any idea what to use on this scenario? I'm
using
ADO.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, August 28, 2002 10:50 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Maybe you can try this, it has worked for me before.
Create a new table with 2 columns : YEAR and NextTranNo. it will store the
next TranNo for use in that particular year, so you would have one row per
year. You could populate it now for 2003, 2004 etc with a "1" for each
year, you see?
So when a user's process needs transaction number, it locks this table. if
this table is locked when it tries to lock it, it retries until it gets a
lock (error handler, resume). Once it has a lock, it reads the value, then
updates it to value + 1. then it releases its lock, and goes ahead to
generate its number and do whatever it needs to do.
The delays it might encounter re-trying to get a lock will be very small
because any single client only has it locked long enough to read it, add one
to it, and write it back out immediately, so this will be minimal. There is
no chance that a dupe will occur, my experience is that no matter how small
that chance is eventually it will happen, and Murphy's law says it will
happen at a most inconvenient time.
dim r as dao.recordset
dim strSQL as string
strSQL = "Select * from YearTranNo where YEAR = " & intYear
set r = currentdb.OpenRecordset(strSQL, , dbDenyRead + dbDenyWrite) '
creates pessimistic lock
intNextTranNo = r!NextTranNo
r!NextTranNo = intNextTranNo + 1
r.update ' lock released
r.close
set r = nothing
-----Original Message-----
From: enZo :-) [mailto:enzaux@g...]
Sent: Tuesday, August 27, 2002 9:21 PM
To: Access
Subject: [access] RE: Users Save at the Same Time
Actually what I was thinking is lock the table so that no one could
save from the
table except me. But my problem is how can I know programmatically that the
table is lock
or is used by another user? So that I can have a code that will triggerts
to go back to the
the finmax-addone procedure if it finds the table locked.
is this possible? Or any better ideas.
Thanks,
Enzo
-----Original Message-----
From: simon larsen [mailto:Simon.larsen@h...]
Sent: Wednesday, August 28, 2002 6:26 AM
To: ms_access@y...
Subject: RE: [ms_access] Users Save at the Same Time
Instead of creating the tran no while the user is still doing stuff
create it as a default value on the table and don't let users edit the
table or a bound form to the table. (this in itself could be tricky as I
haven't had to do that much with access default values but someone here
has no doubt done it).
There is still a small chance of duplication but it becomes so damn
small that I don't know if it could actually happen in real life.
Alternatively (and by no means as good) if you are using bound forms
have the transaction no created as soon as the users start entering into
a record. Here you'll have issues if they cancel the insert though.
Simon Larsen
DBA
<e.law> australia pty ltd
Mobile 0401 588 016
Phone (02) 8270 6237
Email address at the Commission Simon.larsen@h...
Email address at elaw s.larsen@e...
> From: enZo :-) [mailto:enzaux@g...]
>
> Hi!! This is somehow a continuation of the "Primary
> Key" saga. I have this
> sample data for clearer view of my problem.
>
>
> TranID BranchID TranDate TranNo
> --------------------------------------
> 1 01 JAN 01 2002 00001 ---> 02-0001
> 2 01 JAN 02 2002 00002 ---> 02-0002
> 3 01 JAN 01 2003 00001 ---> 03-0001
> 4 01 JAN 02 2003 00002 ---> 03-0002
>
>
> My Transaction Number consists of three fields in the table,
> BranchID, TranDate and TranNo,
> so the result would look like for ex. CWA-02-0001. I have
> follwed ur suggestion to use
> another field as my primary key of the table. So I've
> created another field (TranID)
> as my primary key of the table. Yearly TranNo resets to 0001
> so next year there would be
> a transaction number CWA-03-0001.
>
> TranNo is created programatically (findmax-addone method).
> My problem is when two users
> save at the same time, both users get the same TranNo (eg
> both will have CWA-02-0002).
> I know what part goes wrong, it's in the creation of the next
> TranNo (findmax-addone)
> because when they save at the same time they tend to get the
> same max number and add one
> the same time giving them the same result. If TranNo is my
> primary key there would be
> no problem because I can put error handler on primary key
> error but unfortunately TranNo
> is not my primary key.
>
> Any suggestions on how would I make way for this problem?
>
> Thanks,
------------------------ Yahoo! Groups Sponsor ---------------------~-->
4 DVDs Free +s&p Join Now
http://us.click.yahoo.com/pt6YBB/NXiEAA/MVfIAA/q7folB/TM
---------------------------------------------------------------------~->
http://groups.yahoo.com/group/visual-basic-beginners/
http://groups.yahoo.com/group/ms_excel
http://groups.yahoo.com/group/screenprinting
http://groups.yahoo.com/group/powerpoint
http://groups.yahoo.com/group/pkunzip
http://groups.yahoo.com/group/photoshop-beginners
http://groups.yahoo.com/group/ms_access
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
|
|
|
View Cart
