|
 |
access thread: Quick question
Message #1 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Fri, 20 Sep 2002 14:29:20 +0800
|
|
Does Access supports this type of insert statement? Because it seems that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #2 by "Haslett, Andrew" <andrew.haslett@i...> on Fri, 20 Sep 2002 16:13:57 +0930
|
|
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #3 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Fri, 20 Sep 2002 14:58:34 +0800
|
|
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #4 by "Carnley, Dave" <dcarnley@a...> on Fri, 20 Sep 2002 09:23:49 -0500
|
|
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #5 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Sat, 21 Sep 2002 09:24:26 +0800
|
|
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #6 by "Carnley, Dave" <dcarnley@a...> on Mon, 23 Sep 2002 09:18:42 -0500
|
|
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #7 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Tue, 24 Sep 2002 09:21:41 +0800
|
|
Do you think this statement will not create duplicate values as a replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #8 by "Haslett, Andrew" <andrew.haslett@i...> on Tue, 24 Sep 2002 11:26:12 +0930
|
|
If you'd used our suggestions months ago then you wouldn't have these
problems.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, 24 September 2002 10:52 AM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #9 by jose.johnson@j... on Tue, 24 Sep 2002 11:08:59 +0900
|
|
It depends your locking system & error handling.
Now for example, let us assume that two people are simultaneously trying to
insert new records into one table.
according to the current logic, it will add the latest number + 1 as the
new id. It seems working fine. But I think we need
to consider the timing of that "SELECT Max(MyKey)+1" Statement. Suppose
you have more records in your table
and user A issue the sql and he/she get the new id as 25001. the same time
user B issues the same sql and
he/she may also get the same 25001. i think this is due to the executions
time of that SQL Statement. So I think still
there is a possibility to generate duplicate values by this sql.
Best Regards
enzaux@g...
To: access@p...
09/24/2002 10:21 cc:
AM Subject: [access] RE: Quick question
Please respond to
access
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #10 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Tue, 24 Sep 2002 10:30:27 +0800
|
|
I've tried this one saving 3 pc at the same time and there
were no duplicates occurred. Only the tables have only 50 records.
I've use adlockpessimistic.
Thanks,
Enzo
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Tuesday, September 24, 2002 10:09 AM
To: Access
Subject: [access] RE: Quick question
It depends your locking system & error handling.
Now for example, let us assume that two people are simultaneously trying to
insert new records into one table.
according to the current logic, it will add the latest number + 1 as the
new id. It seems working fine. But I think we need
to consider the timing of that "SELECT Max(MyKey)+1" Statement. Suppose
you have more records in your table
and user A issue the sql and he/she get the new id as 25001. the same time
user B issues the same sql and
he/she may also get the same 25001. i think this is due to the executions
time of that SQL Statement. So I think still
there is a possibility to generate duplicate values by this sql.
Best Regards
enzaux@g...
To: access@p...
09/24/2002 10:21 cc:
AM Subject: [access] RE: Quick question
Please respond to
access
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #11 by jose.johnson@j... on Tue, 24 Sep 2002 12:03:28 +0900
|
|
if your records are only 50 there were no chance to happen this.
(computer is faster than user in this case the case where computer is lower
than user then....?)
I think you need to try with some table where more number of records. I
only consider the calculation time of that max(number)
if this take time and that gap is what should be considered.
Rest it looks fine and working
Best Regards
enzaux@g...
To: access@p...
09/24/2002 11:30 cc:
AM Subject: [access] RE: Quick question
Please respond to
access
I've tried this one saving 3 pc at the same time and there
were no duplicates occurred. Only the tables have only 50 records.
I've use adlockpessimistic.
Thanks,
Enzo
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Tuesday, September 24, 2002 10:09 AM
To: Access
Subject: [access] RE: Quick question
It depends your locking system & error handling.
Now for example, let us assume that two people are simultaneously trying to
insert new records into one table.
according to the current logic, it will add the latest number + 1 as the
new id. It seems working fine. But I think we need
to consider the timing of that "SELECT Max(MyKey)+1" Statement. Suppose
you have more records in your table
and user A issue the sql and he/she get the new id as 25001. the same time
user B issues the same sql and
he/she may also get the same 25001. i think this is due to the executions
time of that SQL Statement. So I think still
there is a possibility to generate duplicate values by this sql.
Best Regards
enzaux@g...
To:
access@p...
09/24/2002 10:21 cc:
AM Subject: [access] RE: Quick
question
Please respond to
access
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #12 by "Carnley, Dave" <dcarnley@a...> on Tue, 24 Sep 2002 10:02:30 -0500
|
|
There is still a chance it will happen. If two users execute this query at
the same time (or close enough so that both reads hit before either update)
then 2 users could get the same value for "max(mykey)".
If you really really need to set incremental keys 'manually' in a multi-user
environment, you have to use some specific locking logic. Here are 2 ways
to approach it that I have used:
This could be as simple as, in the procedure where you execute this INSERT,
use an error handler that looks for "duplicate key" errors from the
database. If the error is a duplicate key error, simply re-try your insert
again, and depending on the level of activity in your system (number of
users, frequency of inserts) the chances of colliding twice in a row are
slim.
Or you can place an explicit lock on that table, get the max, do your
insert, and release the lock. If you go this route then you have to write
your INSERT procedure to wait for the lock to clear, ADO/ODBC will wait a
short time (query timeout) for the lock to clear so if you only have the
table locked for the moment it takes to execute the INSERT statement then
this will be mostly transparent. It wouldn't hurt to have an error handler
that re-tried when it detects an ODBC timeout, again depending on how busy
your system is.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 8:22 PM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #13 by "Carnley, Dave" <dcarnley@a...> on Tue, 24 Sep 2002 10:06:50 -0500
|
|
your ability to submit 2 transaction at the same moment is limited by many
factors, pressing enter at the same time is not accurate enough. try this -
write a loop that slams inserts to your db at full speed over and over in a
loop without any delays.
x=0
do while x < 1000
{insert}
x=x+1
loop
start this running on 3 pcs at the same time, and you may see different
results.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 9:30 PM
To: Access
Subject: [access] RE: Quick question
I've tried this one saving 3 pc at the same time and there
were no duplicates occurred. Only the tables have only 50 records.
I've use adlockpessimistic.
Thanks,
Enzo
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Tuesday, September 24, 2002 10:09 AM
To: Access
Subject: [access] RE: Quick question
It depends your locking system & error handling.
Now for example, let us assume that two people are simultaneously trying to
insert new records into one table.
according to the current logic, it will add the latest number + 1 as the
new id. It seems working fine. But I think we need
to consider the timing of that "SELECT Max(MyKey)+1" Statement. Suppose
you have more records in your table
and user A issue the sql and he/she get the new id as 25001. the same time
user B issues the same sql and
he/she may also get the same 25001. i think this is due to the executions
time of that SQL Statement. So I think still
there is a possibility to generate duplicate values by this sql.
Best Regards
enzaux@g...
To: access@p...
09/24/2002 10:21 cc:
AM Subject: [access] RE: Quick
question
Please respond to
access
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #14 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Wed, 25 Sep 2002 12:19:37 +0800
|
|
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, September 24, 2002 11:03 PM
To: Access
Subject: [access] RE: Quick question
There is still a chance it will happen. If two users execute this query at
the same time (or close enough so that both reads hit before either update)
then 2 users could get the same value for "max(mykey)".
If you really really need to set incremental keys 'manually' in a multi-user
environment, you have to use some specific locking logic. Here are 2 ways
to approach it that I have used:
This could be as simple as, in the procedure where you execute this INSERT,
use an error handler that looks for "duplicate key" errors from the
database. If the error is a duplicate key error, simply re-try your insert
again, and depending on the level of activity in your system (number of
users, frequency of inserts) the chances of colliding twice in a row are
slim.
Or you can place an explicit lock on that table, get the max, do your
insert, and release the lock. If you go this route then you have to write
your INSERT procedure to wait for the lock to clear, ADO/ODBC will wait a
short time (query timeout) for the lock to clear so if you only have the
table locked for the moment it takes to execute the INSERT statement then
this will be mostly transparent. It wouldn't hurt to have an error handler
that re-tried when it detects an ODBC timeout, again depending on how busy
your system is.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 8:22 PM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #15 by "Carnley, Dave" <dcarnley@a...> on Wed, 25 Sep 2002 09:30:03 -0500
|
|
using ADO you lock a table using options in the OPEN or EXECUTE methods...
for example adLockPessimistic. Look at the documentation for more detail
than I could ever type out here ;)
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, September 24, 2002 11:20 PM
To: Access
Subject: [access] RE: Quick question
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, September 24, 2002 11:03 PM
To: Access
Subject: [access] RE: Quick question
There is still a chance it will happen. If two users execute this query at
the same time (or close enough so that both reads hit before either update)
then 2 users could get the same value for "max(mykey)".
If you really really need to set incremental keys 'manually' in a multi-user
environment, you have to use some specific locking logic. Here are 2 ways
to approach it that I have used:
This could be as simple as, in the procedure where you execute this INSERT,
use an error handler that looks for "duplicate key" errors from the
database. If the error is a duplicate key error, simply re-try your insert
again, and depending on the level of activity in your system (number of
users, frequency of inserts) the chances of colliding twice in a row are
slim.
Or you can place an explicit lock on that table, get the max, do your
insert, and release the lock. If you go this route then you have to write
your INSERT procedure to wait for the lock to clear, ADO/ODBC will wait a
short time (query timeout) for the lock to clear so if you only have the
table locked for the moment it takes to execute the INSERT statement then
this will be mostly transparent. It wouldn't hurt to have an error handler
that re-tried when it detects an ODBC timeout, again depending on how busy
your system is.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 8:22 PM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #16 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Thu, 26 Sep 2002 10:31:42 +0800
|
|
I thought adlockpessimistic locks only records not the table.
By they way I have tried looping the INSERT statement and you're
right it duplicate occurs. May be I'll just have an error handler.
But I'm still finding ways to work around with this. I'm doing this
for my learning purposes. But in a way I use this also on my current
project.
I have also used ur suggestion previously about having a seperate
table that will hold the seed value or should we say the max value of
a tran no. But I'm still doing test on it on how reliable it is that no
duplicates would occur. Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited? When we have
this statement "rs!Field1 = FieldValue" ? Does it mean that
we are already editing the record?
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, September 25, 2002 10:30 PM
To: Access
Subject: [access] RE: Quick question
using ADO you lock a table using options in the OPEN or EXECUTE methods...
for example adLockPessimistic. Look at the documentation for more detail
than I could ever type out here ;)
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, September 24, 2002 11:20 PM
To: Access
Subject: [access] RE: Quick question
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, September 24, 2002 11:03 PM
To: Access
Subject: [access] RE: Quick question
There is still a chance it will happen. If two users execute this query at
the same time (or close enough so that both reads hit before either update)
then 2 users could get the same value for "max(mykey)".
If you really really need to set incremental keys 'manually' in a multi-user
environment, you have to use some specific locking logic. Here are 2 ways
to approach it that I have used:
This could be as simple as, in the procedure where you execute this INSERT,
use an error handler that looks for "duplicate key" errors from the
database. If the error is a duplicate key error, simply re-try your insert
again, and depending on the level of activity in your system (number of
users, frequency of inserts) the chances of colliding twice in a row are
slim.
Or you can place an explicit lock on that table, get the max, do your
insert, and release the lock. If you go this route then you have to write
your INSERT procedure to wait for the lock to clear, ADO/ODBC will wait a
short time (query timeout) for the lock to clear so if you only have the
table locked for the moment it takes to execute the INSERT statement then
this will be mostly transparent. It wouldn't hurt to have an error handler
that re-tried when it detects an ODBC timeout, again depending on how busy
your system is.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 8:22 PM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #17 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Thu, 26 Sep 2002 10:32:45 +0800
|
|
Thanks Jose
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Tuesday, September 24, 2002 11:03 AM
To: Access
Subject: [access] RE: Quick question
if your records are only 50 there were no chance to happen this.
(computer is faster than user in this case the case where computer is lower
than user then....?)
I think you need to try with some table where more number of records. I
only consider the calculation time of that max(number)
if this take time and that gap is what should be considered.
Rest it looks fine and working
Best Regards
enzaux@g...
To: access@p...
09/24/2002 11:30 cc:
AM Subject: [access] RE: Quick question
Please respond to
access
I've tried this one saving 3 pc at the same time and there
were no duplicates occurred. Only the tables have only 50 records.
I've use adlockpessimistic.
Thanks,
Enzo
-----Original Message-----
From: jose.johnson@j... [mailto:jose.johnson@j...]
Sent: Tuesday, September 24, 2002 10:09 AM
To: Access
Subject: [access] RE: Quick question
It depends your locking system & error handling.
Now for example, let us assume that two people are simultaneously trying to
insert new records into one table.
according to the current logic, it will add the latest number + 1 as the
new id. It seems working fine. But I think we need
to consider the timing of that "SELECT Max(MyKey)+1" Statement. Suppose
you have more records in your table
and user A issue the sql and he/she get the new id as 25001. the same time
user B issues the same sql and
he/she may also get the same 25001. i think this is due to the executions
time of that SQL Statement. So I think still
there is a possibility to generate duplicate values by this sql.
Best Regards
enzaux@g...
To:
access@p...
09/24/2002 10:21 cc:
AM Subject: [access] RE: Quick
question
Please respond to
access
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #18 by "Carnley, Dave" <dcarnley@a...> on Thu, 26 Sep 2002 07:54:56 -0500
|
|
"Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited?"
If you open a recordset with adLockPessimistic, the database record becomes
locked when you update a field's value. So you need to have an erro handler
that will capture the error (#3260) and do whatever your app needs to do for
a locked record (like try the command again or show an error message...)
on error goto errhandler
retrycount = 0
r = conn.open... adLockPessimistic
r!field = r!field + 1 ' ADO tried to get lock here; if it can't, go to
errorhandler and try again
r.update ' lock released here
r.close
...
errhandler:
if error code = 3260 then
if retrycount < 5 then
resume ' RESUME makes it try the errored stmt again
else 'handle the error - tried 5 times could not get lock
endif
else 'handle the error its not locking...
endif
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Wednesday, September 25, 2002 9:32 PM
To: Access
Subject: [access] RE: Quick question
I thought adlockpessimistic locks only records not the table.
By they way I have tried looping the INSERT statement and you're
right it duplicate occurs. May be I'll just have an error handler.
But I'm still finding ways to work around with this. I'm doing this
for my learning purposes. But in a way I use this also on my current
project.
I have also used ur suggestion previously about having a seperate
table that will hold the seed value or should we say the max value of
a tran no. But I'm still doing test on it on how reliable it is that no
duplicates would occur. Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited? When we have
this statement "rs!Field1 = FieldValue" ? Does it mean that
we are already editing the record?
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, September 25, 2002 10:30 PM
To: Access
Subject: [access] RE: Quick question
using ADO you lock a table using options in the OPEN or EXECUTE methods...
for example adLockPessimistic. Look at the documentation for more detail
than I could ever type out here ;)
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, September 24, 2002 11:20 PM
To: Access
Subject: [access] RE: Quick question
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Tuesday, September 24, 2002 11:03 PM
To: Access
Subject: [access] RE: Quick question
There is still a chance it will happen. If two users execute this query at
the same time (or close enough so that both reads hit before either update)
then 2 users could get the same value for "max(mykey)".
If you really really need to set incremental keys 'manually' in a multi-user
environment, you have to use some specific locking logic. Here are 2 ways
to approach it that I have used:
This could be as simple as, in the procedure where you execute this INSERT,
use an error handler that looks for "duplicate key" errors from the
database. If the error is a duplicate key error, simply re-try your insert
again, and depending on the level of activity in your system (number of
users, frequency of inserts) the chances of colliding twice in a row are
slim.
Or you can place an explicit lock on that table, get the max, do your
insert, and release the lock. If you go this route then you have to write
your INSERT procedure to wait for the lock to clear, ADO/ODBC will wait a
short time (query timeout) for the lock to clear so if you only have the
table locked for the moment it takes to execute the INSERT statement then
this will be mostly transparent. It wouldn't hurt to have an error handler
that re-tried when it detects an ODBC timeout, again depending on how busy
your system is.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Monday, September 23, 2002 8:22 PM
To: Access
Subject: [access] RE: Quick question
Do you think this statement will not create duplicate values as a
replacement for
autonumber in a multiuser environment?
"INSERT INTO Table1 SELECT Max(MyKey)+1 FROM Table1"
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Monday, September 23, 2002 10:19 PM
To: Access
Subject: [access] RE: Quick question
That's a good reason :)
In a multi-user system logic like this will eventually cause duplicated IDs
unless you use locking logic to prevent it.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 8:24 PM
To: Access
Subject: [access] RE: Quick question
OIC, thanks!!! I don't use autonumbers because I need
sequential numbers. Sometimes there are situations wherein
the transaction is rolled back. In autonumbers I can't use
the number that was used by a rolled back trans.
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Friday, September 20, 2002 10:24 PM
To: Access
Subject: [access] RE: Quick question
it works but you have a syntax error in your SQL
strSQL = "INSERT INTO tblBooklets (BookletNo) SELECT
Max(BookletNo)+1 FROM tblBooklets"
don't use VALUES()
of course this is a lot easier with autonumber...
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 20, 2002 1:59 AM
To: Access
Subject: [access] RE: Quick question
I got this error:
-2147217900 - Syntax Error in query expression 'SELECT
Max(BookletNo)+1 FROM tblBooklets'
Thanks,
Enzo
-----Original Message-----
From: Haslett, Andrew [mailto:andrew.haslett@i...]
Sent: Friday, September 20, 2002 2:44 PM
To: Access
Subject: [access] RE: Quick question
what happened when you tried it?
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, 20 September 2002 3:59 PM
To: Access
Subject: [access] Quick question
Does Access supports this type of insert statement? Because it seems
that I get an error
on the SQL statement I'm using below, specifically on the SELECT part.
~~~~~~~~~~~~~~~~~~~~
strSQL = "INSERT INTO tblBooklets (BookletNo) VALUES (SELECT
Max(BookletNo)+1 FROM tblBooklets)"
dbconn.Open CONNSTR
cmd.ActiveConnection = dbconn
cmd.CommandText = strSQL
cmd.CommandType = adCmdText
cmd.Execute
~~~~~~~~~~~~~~~~~~~~
Thanks,
Enzo
YahooID: onestepcloser2insanity
Message #19 by "Lorenzo Amado Zaragoza" <enzaux@g...> on Fri, 27 Sep 2002 13:32:14 +0800
|
|
OIC so upon UPDATE the lock is realeased unlike in adLockOptimistic the lock is enable
only upon UPDATE. Thanks Dave for making some easy things for me :D
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Thursday, September 26, 2002 8:55 PM
To: Access
Subject: [access] RE: Quick question
"Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited?"
If you open a recordset with adLockPessimistic, the database record becomes
locked when you update a field's value. So you need to have an erro handler
that will capture the error (#3260) and do whatever your app needs to do for
a locked record (like try the command again or show an error message...)
on error goto errhandler
retrycount = 0
r = conn.open... adLockPessimistic
r!field = r!field + 1 ' ADO tried to get lock here; if it can't, go to
errorhandler and try again
r.update ' lock released here
r.close
...
errhandler:
if error code = 3260 then
if retrycount < 5 then
resume ' RESUME makes it try the errored stmt again
else 'handle the error - tried 5 times could not get lock
endif
else 'handle the error its not locking...
endif
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Wednesday, September 25, 2002 9:32 PM
To: Access
Subject: [access] RE: Quick question
I thought adlockpessimistic locks only records not the table.
By they way I have tried looping the INSERT statement and you're
right it duplicate occurs. May be I'll just have an error handler.
But I'm still finding ways to work around with this. I'm doing this
for my learning purposes. But in a way I use this also on my current
project.
I have also used ur suggestion previously about having a seperate
table that will hold the seed value or should we say the max value of
a tran no. But I'm still doing test on it on how reliable it is that no
duplicates would occur. Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited? When we have
this statement "rs!Field1 = FieldValue" ? Does it mean that
we are already editing the record?
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, September 25, 2002 10:30 PM
To: Access
Subject: [access] RE: Quick question
using ADO you lock a table using options in the OPEN or EXECUTE methods...
for example adLockPessimistic. Look at the documentation for more detail
than I could ever type out here ;)
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, September 24, 2002 11:20 PM
To: Access
Subject: [access] RE: Quick question
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
Message #20 by "Carnley, Dave" <dcarnley@a...> on Fri, 27 Sep 2002 09:31:21 -0500
|
|
That is my understanding from reading MSDN. Until I went and read it again
yesterday, I had thought that the lock was placed when you opened the
recordset. So everybody learns something :)
Optimistic locking is good for system with low collision rates, where few
users are active at any given time or there are few updates to records.
When you read data getting an optimistic lock, the server actually stores a
copy of the data you read, but no lock is placed on the table. When you send
your update, the server compares what is actually on the table at that
moment to what you originally read to start your transaction, and if it is
different, that means that somebody else updated that record between your
read and your commit. So your transaction fails, you have to re-read the
data and verify if, given the updated data, if your user still wants to
commit their update. This is more work for your app than what it has to do
on a pessimistic lock collision, however if the chances of a collision are
very small, it is much easier and faster.
So on an orders table optimistic might be appropriate, but on an ID number
table, pessimistic is called for.
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Friday, September 27, 2002 12:32 AM
To: Access
Subject: [access] RE: Quick question
OIC so upon UPDATE the lock is realeased unlike in adLockOptimistic
the lock is enable
only upon UPDATE. Thanks Dave for making some easy things for me :D
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Thursday, September 26, 2002 8:55 PM
To: Access
Subject: [access] RE: Quick question
"Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited?"
If you open a recordset with adLockPessimistic, the database record becomes
locked when you update a field's value. So you need to have an erro handler
that will capture the error (#3260) and do whatever your app needs to do for
a locked record (like try the command again or show an error message...)
on error goto errhandler
retrycount = 0
r = conn.open... adLockPessimistic
r!field = r!field + 1 ' ADO tried to get lock here; if it can't, go to
errorhandler and try again
r.update ' lock released here
r.close
...
errhandler:
if error code = 3260 then
if retrycount < 5 then
resume ' RESUME makes it try the errored stmt again
else 'handle the error - tried 5 times could not get lock
endif
else 'handle the error its not locking...
endif
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Wednesday, September 25, 2002 9:32 PM
To: Access
Subject: [access] RE: Quick question
I thought adlockpessimistic locks only records not the table.
By they way I have tried looping the INSERT statement and you're
right it duplicate occurs. May be I'll just have an error handler.
But I'm still finding ways to work around with this. I'm doing this
for my learning purposes. But in a way I use this also on my current
project.
I have also used ur suggestion previously about having a seperate
table that will hold the seed value or should we say the max value of
a tran no. But I'm still doing test on it on how reliable it is that no
duplicates would occur. Actually locking sometimes confuses me, as
adLockPessimistic is defined it will lock the record AFTER the record
is edited but when can we say that a record is being edited? When we have
this statement "rs!Field1 = FieldValue" ? Does it mean that
we are already editing the record?
Thanks,
Enzo
-----Original Message-----
From: Carnley, Dave [mailto:dcarnley@a...]
Sent: Wednesday, September 25, 2002 10:30 PM
To: Access
Subject: [access] RE: Quick question
using ADO you lock a table using options in the OPEN or EXECUTE methods...
for example adLockPessimistic. Look at the documentation for more detail
than I could ever type out here ;)
-----Original Message-----
From: Lorenzo Amado Zaragoza [mailto:enzaux@g...]
Sent: Tuesday, September 24, 2002 11:20 PM
To: Access
Subject: [access] RE: Quick question
Or you can place an explicit lock on that table
>>> how can you lock the table? I'm just curious.
Thanks,
Enzo
|
|
|
View Cart
