p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

access thread: Saving Record at the same time


Message #1 by "enZo :-\)" <enzaux@g...> on Sat, 15 Jun 2002 11:57:41 +0800
	Hi!  I have this Database that contains receipt transactions.  Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase programatically.

	My problem arises when two or more people try to save at the same time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1 they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo


Message #2 by "Phillip Johnson" <phillip.johnson@e...> on Sat, 15 Jun 2002 14:08:31
Hi Enzo,

I assume that the first record to commit saves and then the second record 
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it 
occurs, retrieve the lase number from the table again, increment it, give 
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new 
number is given, the code then resumes at the point where the save was 
tried.  This way if the same thing happened again and another user was 
trying to add a record and had got the same number, the error will be 
trapped again and the next sequential number will be tried.  The record 
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then
   
             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the 
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.

    

> 
	Hi!  I have this Database that contains receipt transactions.  
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when 
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase 
programatically.

	My problem arises when two or more people try to save at the same 
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber 
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being 
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1 
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I 
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not 
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo


Message #3 by "enZo :-\)" <enzaux@g...> on Mon, 17 Jun 2002 09:04:38 +0800
To Neil:
	Sorry Neil I dont wan't to use autonumber because the number jumps if the transaction is cancelled.  I need continous number as
much as possible.  If a particular transaction is cancelled the tranx number should still be reusable for another transaction.

To Phillip:
	Well actually I do have an error trapping the only thing I missed is the IF-THEN for the error type :)  Thanks for that info.
That really makes me crazy if there's an error everytin goes to the error event even not a primary key error.

	Where can I find the error number?  Thanks.

To everyone:
	I had this friend of mine that uses power builder.  He said to me that there is a command he used in SQL where it will lock the
table so that he can save his transaction before letting others to save.  He uses SQL Commit and set it to true then after saving
he set it to false.  Is there something like it in Access?

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo





Message #4 by "enZo :-\)" <enzaux@g...> on Mon, 17 Jun 2002 13:37:15 +0800
	Thanks Phillip for the tips regarding the IF THEN of Error Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo





Message #5 by "Phillip Johnson" <phillip.johnson@e...> on Mon, 17 Jun 2002 15:43:09
How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo





Message #6 by "enZo :-\)" <enzaux@g...> on Tue, 18 Jun 2002 08:16:18 +0800
I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo








Message #7 by "Phillip Johnson" <phillip.johnson@e...> on Tue, 18 Jun 2002 08:39:21
You should get an error raised if the field being duplicated is a unique 
primary key.  Post the procedure that saves and I will check it out and 
see whats up.  (Just out of curiosity you havent go "On Error Resume Next" 
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo








Message #8 by "enZo :-\)" <enzaux@g...> on Tue, 18 Jun 2002 16:46:39 +0800
private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 8:39 AM
To: Access
Subject: [access] Re: Saving Record at the same time


You should get an error raised if the field being duplicated is a unique 
primary key.  Post the procedure that saves and I will check it out and 
see whats up.  (Just out of curiosity you havent go "On Error Resume Next" 
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo











Message #9 by "Phillip Johnson" <phillip.johnson@e...> on Tue, 18 Jun 2002 13:32:28
Enzo, I dont know which version of Access you are using, but I am using 97 
and I think that the database object you declare is a DAO object.  You 
should get an error with the code, IF there is indeed a duplicate primary 
key.  Your best bet is to:

FIRST:

Check that the ID field is a primary field and duplicates are not 
allowed.  While you are on, check that its the only field in the table 
that is part of the primary key field.

SECOND:

put a break point on the line of code that executes the SQL command.  When 
the code breaks, check the value in the primary key.  Make sure that the 
second value is indeed a duplicate of the first.  

THIRD:

After trying to save two records with the same primary key, check the 
database and see if both records have been inserted and if they have 
indeed created a duplicate primary key.

You should definitely get an error if you try to save a second error with 
the same primary key value as another record in the table.

Good luck.


> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES 
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 8:39 AM
To: Access
Subject: [access] Re: Saving Record at the same time


You should get an error raised if the field being duplicated is a unique 
primary key.  Post the procedure that saves and I will check it out and 
see whats up.  (Just out of curiosity you havent go "On Error Resume Next" 
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo











Message #10 by "Amy Wyatt" <amyw@c...> on Tue, 18 Jun 2002 13:39:54
I have also experienced this with SQL databases. the DAO process you are 
using, for some reason, does not return an error. It just does not add the 
data. (Is this what you are experiencing?) The way to get around it is to 
use ADO and actually open the action query instead of executing it. This 
will then produce the error #-2147467259 and you can trap it, add 1 to the 
key value and try again in a loop until it does not return an error. I 
have tried executing the SQL in ADO but that also does not return an 
error. You have to actually open (and therefore run) the action query to 
get an error to fire.

Hope this helps.

Amy

> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES 
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 8:39 AM
To: Access
Subject: [access] Re: Saving Record at the same time


You should get an error raised if the field being duplicated is a unique 
primary key.  Post the procedure that saves and I will check it out and 
see whats up.  (Just out of curiosity you havent go "On Error Resume Next" 
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo











Message #11 by "enZo :-\)" <enzaux@g...> on Wed, 19 Jun 2002 08:35:42 +0800
Phillip:
	Actually you are right it is DAO :)  I'm using access 2000.  Thanks for the advice.  Right know I'm sticking with the first
solution you gave ( trapping the error number) and it work great!  Thanks

Enzo :)

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 1:32 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Enzo, I dont know which version of Access you are using, but I am using 97
and I think that the database object you declare is a DAO object.  You
should get an error with the code, IF there is indeed a duplicate primary
key.  Your best bet is to:

FIRST:

Check that the ID field is a primary field and duplicates are not
allowed.  While you are on, check that its the only field in the table
that is part of the primary key field.

SECOND:

put a break point on the line of code that executes the SQL command.  When
the code breaks, check the value in the primary key.  Make sure that the
second value is indeed a duplicate of the first.

THIRD:

After trying to save two records with the same primary key, check the
database and see if both records have been inserted and if they have
indeed created a duplicate primary key.

You should definitely get an error if you try to save a second error with
the same primary key value as another record in the table.

Good luck.


> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 8:39 AM
To: Access
Subject: [access] Re: Saving Record at the same time


You should get an error raised if the field being duplicated is a unique
primary key.  Post the procedure that saves and I will check it out and
see whats up.  (Just out of curiosity you havent go "On Error Resume Next"
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

>
	Thanks Phillip for the tips regarding the IF THEN of Error
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo














Message #12 by "enZo :-\)" <enzaux@g...> on Wed, 19 Jun 2002 08:37:29 +0800
	Hi Amy!!!  Yes that's what I'm experiencing with DAO.  How would I OPEN a query instead of EXECUTING it using ADO?

Thanks,

Enzo

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, June 18, 2002 1:40 PM
To: Access
Subject: [access] Re: Saving Record at the same time


I have also experienced this with SQL databases. the DAO process you are 
using, for some reason, does not return an error. It just does not add the 
data. (Is this what you are experiencing?) The way to get around it is to 
use ADO and actually open the action query instead of executing it. This 
will then produce the error #-2147467259 and you can trap it, add 1 to the 
key value and try again in a loop until it does not return an error. I 
have tried executing the SQL in ADO but that also does not return an 
error. You have to actually open (and therefore run) the action query to 
get an error to fire.

Hope this helps.

Amy

> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES 
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Tuesday, June 18, 2002 8:39 AM
To: Access
Subject: [access] Re: Saving Record at the same time


You should get an error raised if the field being duplicated is a unique 
primary key.  Post the procedure that saves and I will check it out and 
see whats up.  (Just out of curiosity you havent go "On Error Resume Next" 
anywhere have you?)

> I'm using the ADO command to execute the SQL

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Monday, June 17, 2002 3:43 PM
To: Access
Subject: [access] Re: Saving Record at the same time


How are you executing the SQL?  is it through an activeX command object?

> 
	Thanks Phillip for the tips regarding the IF THEN of Error 
Number.  But what if I tried to save data through SQL (hard coding),
how would I do the same process I do in Access.  Because I've noticed that 
when I try the saving manually (hard coding) no error
occurs or any message saying that the record was not save because there is 
already in a conflict on the primary key.

Thanks,

Enzo

-----Original Message-----
From: Phillip Johnson [mailto:phillip.johnson@e...]
Sent: Saturday, June 15, 2002 2:09 PM
To: Access
Subject: [access] Re: Saving Record at the same time


Hi Enzo,

I assume that the first record to commit saves and then the second record
that tries to commit with the same primary key throws an error.

I would try having an errortrap that trapped for that error and, when it
occurs, retrieve the lase number from the table again, increment it, give
it to your invalid record and try saving it again.

Try and write your code so that when the error is trapped and the new
number is given, the code then resumes at the point where the save was
tried.  This way if the same thing happened again and another user was
trying to add a record and had got the same number, the error will be
trapped again and the next sequential number will be tried.  The record
will eventually get lucky and save.

Construct your code like this:

START SUB

on error goto errorhandler

    Any code up to the point where you need to save the record here,
    including creating the new record, but not saving it.

SAVEPOINT:

    Code to commit the change here

EXIT SUB
ERRORHANDLER:

    IF ERR.NUMBER = (duplicate primary key error number here) then

             Code to retrieve the number of the last commited
             record, increment it by one and assign it to the
             record you are trying to save here

         RESUME SAVEPOINT

     END IF

END SUB

Hope this helps.



>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo














Message #13 by "Amy Wyatt" <amyw@c...> on Wed, 19 Jun 2002 14:23:58
I am assuming that you are using an Access DB rather than an Access 
Project and you tables are linked via ODBC or another such linking. That 
said, to open a query is like the following:

Dim rstQuery as ADODB.Recordset
Dim strSQL as String

Set rstQuery=New ADODB.Recordset

strSQL = "INSERT INTO tblOne . . . "

'This runs the append query
rstQuery.Open strSQL,CurrentProject.Connection,adOpenDynamic, _
              adLockOptimistic, adCmdText

rstQuery.Close
Set rstQuery=Nothing


'If the value you are trying to enter for the primary key has already been 
used, you will get the error message for duplications (# -2147467259) and 
can then trap it and handle it.

Hope this helps.

Amy

> 
	Hi Amy!!!  Yes that's what I'm experiencing with DAO.  How would I 
OPEN a query instead of EXECUTING it using ADO?

Thanks,

Enzo

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, June 18, 2002 1:40 PM
To: Access
Subject: [access] Re: Saving Record at the same time


I have also experienced this with SQL databases. the DAO process you are 
using, for some reason, does not return an error. It just does not add the 
data. (Is this what you are experiencing?) The way to get around it is to 
use ADO and actually open the action query instead of executing it. This 
will then produce the error #-2147467259 and you can trap it, add 1 to the 
key value and try again in a loop until it does not return an error. I 
have tried executing the SQL in ADO but that also does not return an 
error. You have to actually open (and therefore run) the action query to 
get an error to fire.

Hope this helps.

Amy

> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES 
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo


>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo


Message #14 by "enZo :-\)" <enzaux@g...> on Thu, 20 Jun 2002 09:07:05 +0800
	Thanks Amy!!!!!!

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Wednesday, June 19, 2002 2:24 PM
To: Access
Subject: [access] Re: Saving Record at the same time


I am assuming that you are using an Access DB rather than an Access 
Project and you tables are linked via ODBC or another such linking. That 
said, to open a query is like the following:

Dim rstQuery as ADODB.Recordset
Dim strSQL as String

Set rstQuery=New ADODB.Recordset

strSQL = "INSERT INTO tblOne . . . "

'This runs the append query
rstQuery.Open strSQL,CurrentProject.Connection,adOpenDynamic, _
              adLockOptimistic, adCmdText

rstQuery.Close
Set rstQuery=Nothing


'If the value you are trying to enter for the primary key has already been 
used, you will get the error message for duplications (# -2147467259) and 
can then trap it and handle it.

Hope this helps.

Amy

> 
	Hi Amy!!!  Yes that's what I'm experiencing with DAO.  How would I 
OPEN a query instead of EXECUTING it using ADO?

Thanks,

Enzo

-----Original Message-----
From: Amy Wyatt [mailto:amyw@c...]
Sent: Tuesday, June 18, 2002 1:40 PM
To: Access
Subject: [access] Re: Saving Record at the same time


I have also experienced this with SQL databases. the DAO process you are 
using, for some reason, does not return an error. It just does not add the 
data. (Is this what you are experiencing?) The way to get around it is to 
use ADO and actually open the action query instead of executing it. This 
will then produce the error #-2147467259 and you can trap it, add 1 to the 
key value and try again in a loop until it does not return an error. I 
have tried executing the SQL in ADO but that also does not return an 
error. You have to actually open (and therefore run) the action query to 
get an error to fire.

Hope this helps.

Amy

> private sub cmdsave_click()
dim db as database
dim strSQL as string

set db = currentdb

strSQL="INSERT INTO tblTranx (ID, Name, Addr, Sex, Loc) VALUES 
txtid.value, txtname.value, txtaddr.value, " & _
        "txtsex.value, txtloc.text"
db.execute(strSQL)

db.close
set db = nothing

end sub

Thanks,

Enzo


>
	Hi!  I have this Database that contains receipt transactions.
Every transaction has a Transaction Number.  I didn't made this
Field AutoNumber because I know the problem with AutoNumber is that when
the transaction is cancelled it will automatically use the
next number.  So I have done my Transaction number to increase
programatically.

	My problem arises when two or more people try to save at the same
time, I've got an error cause they are having the same
transaction number.  Here is my process how i do the autonumber
programatically:

	1. I query for the last transaction number in the table
	2. Then I increment it by one
	3. Then I assign the transaction number to the transaction being
saved
	4. Save the transaction

	I see that my problem when they save at the same time is at step 1
they are getting the same las transaction number :(  Do you
have any ideas on how I should resolve this problem?  Is there a way I
could lock the table or a record to prevent access from
others.  Is there any sql statement that would help me do this?  I'm not
using DAO coz I'm not used to it.  Please I need ideas on
how should I do this.

Thanks,

Enzo






  Return to Index