|
 |
access thread: Insert Into SQL command and autonumbers
Message #1 by rickdolson@y... on Wed, 20 Nov 2002 22:26:50
|
|
I am trying to write some code in Access 2000 where when I press a button,
I use a the "Insert Into" sql command to insert records into a table.
However, I am having a lot of difficulty because the table has an
autonumber field.
How do you write the sql command to take into account this autonumber?
I'd like the code to insert the records into the table, and the autonumber
to be filled in automatically, or, I need to know how to write the code to
pick the next autonumber to be added with the data.
Thanks for any help you can give me.
Message #2 by "Carnley, Dave" <dcarnley@a...> on Wed, 20 Nov 2002 16:54:58 -0600
|
|
SQL provides a value @@IDENTITY. I do all inserts from stored procedures
and from the S.P. I return that value using "SELECT @@IDENTITY" directly
after the insert statement. That way my sp produces a recordset:
set rs = adoconnection.execute ("sp_ins_table name='joe'...")
NewID = rs.fields(1)
I don't know if @@IDENTITY is available to VBA connection...
-----Original Message-----
From: rickdolson@y... [mailto:rickdolson@y...]
Sent: Wednesday, November 20, 2002 4:27 PM
To: Access
Subject: [access] Insert Into SQL command and autonumbers
I am trying to write some code in Access 2000 where when I press a button,
I use a the "Insert Into" sql command to insert records into a table.
However, I am having a lot of difficulty because the table has an
autonumber field.
How do you write the sql command to take into account this autonumber?
I'd like the code to insert the records into the table, and the autonumber
to be filled in automatically, or, I need to know how to write the code to
pick the next autonumber to be added with the data.
Thanks for any help you can give me.
Message #3 by "Leo Scott" <leoscott@c...> on Wed, 20 Nov 2002 14:56:23 -0800
|
|
The easiest way to do this is build a test query in the QBE then look at the
SQL that Access will generate for you. When you do an INSERT command on a
table with an autonumber field you don't include the autonumber field as one
of the fields that you insert data into.
|-----Original Message-----
|From: rickdolson@y... [mailto:rickdolson@y...]
|Sent: Wednesday, November 20, 2002 10:27 PM
|To: Access
|Subject: [access] Insert Into SQL command and autonumbers
|
|
|I am trying to write some code in Access 2000 where when I press a button,
|I use a the "Insert Into" sql command to insert records into a table.
|However, I am having a lot of difficulty because the table has an
|autonumber field.
|
|How do you write the sql command to take into account this autonumber?
|I'd like the code to insert the records into the table, and the autonumber
|to be filled in automatically, or, I need to know how to write the code to
|pick the next autonumber to be added with the data.
|
|Thanks for any help you can give me.
|
Message #4 by "Leo Scott" <leoscott@c...> on Wed, 20 Nov 2002 15:09:53 -0800
|
|
If he's doing this against a Access database is @@Identity even supported?
|-----Original Message-----
|From: Carnley, Dave [mailto:dcarnley@a...]
|Sent: Wednesday, November 20, 2002 2:55 PM
|To: Access
|Subject: [access] Insert Into SQL command and autonumbers
|
|
|SQL provides a value @@IDENTITY. I do all inserts from stored procedures
|and from the S.P. I return that value using "SELECT @@IDENTITY" directly
|after the insert statement. That way my sp produces a recordset:
|
|set rs = adoconnection.execute ("sp_ins_table name='joe'...")
|NewID = rs.fields(1)
|
|I don't know if @@IDENTITY is available to VBA connection...
|
|
|
|
|-----Original Message-----
|From: rickdolson@y... [mailto:rickdolson@y...]
|Sent: Wednesday, November 20, 2002 4:27 PM
|To: Access
|Subject: [access] Insert Into SQL command and autonumbers
|
|
|I am trying to write some code in Access 2000 where when I press a button,
|I use a the "Insert Into" sql command to insert records into a table.
|However, I am having a lot of difficulty because the table has an
|autonumber field.
|
|How do you write the sql command to take into account this autonumber?
|I'd like the code to insert the records into the table, and the autonumber
|to be filled in automatically, or, I need to know how to write the code to
|pick the next autonumber to be added with the data.
|
|Thanks for any help you can give me.
|
Message #5 by "Carnley, Dave" <dcarnley@a...> on Thu, 21 Nov 2002 09:19:33 -0600
|
|
No, I guess not... I'm not sure why I assumed he was using a SQL back-end :/
-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Wednesday, November 20, 2002 5:10 PM
To: Access
Subject: [access] RE: Insert Into SQL command and autonumbers
If he's doing this against a Access database is @@Identity even supported?
|-----Original Message-----
|From: Carnley, Dave [mailto:dcarnley@a...]
|Sent: Wednesday, November 20, 2002 2:55 PM
|To: Access
|Subject: [access] Insert Into SQL command and autonumbers
|
|
|SQL provides a value @@IDENTITY. I do all inserts from stored procedures
|and from the S.P. I return that value using "SELECT @@IDENTITY" directly
|after the insert statement. That way my sp produces a recordset:
|
|set rs = adoconnection.execute ("sp_ins_table name='joe'...")
|NewID = rs.fields(1)
|
|I don't know if @@IDENTITY is available to VBA connection...
|
|
|
|
|-----Original Message-----
|From: rickdolson@y... [mailto:rickdolson@y...]
|Sent: Wednesday, November 20, 2002 4:27 PM
|To: Access
|Subject: [access] Insert Into SQL command and autonumbers
|
|
|I am trying to write some code in Access 2000 where when I press a button,
|I use a the "Insert Into" sql command to insert records into a table.
|However, I am having a lot of difficulty because the table has an
|autonumber field.
|
|How do you write the sql command to take into account this autonumber?
|I'd like the code to insert the records into the table, and the autonumber
|to be filled in automatically, or, I need to know how to write the code to
|pick the next autonumber to be added with the data.
|
|Thanks for any help you can give me.
|
Message #6 by "Leo Scott" <leoscott@c...> on Thu, 21 Nov 2002 08:14:46 -0800
|
|
Usually, you are soooo good I dare not touch one of your posts. :-)
|-----Original Message-----
|From: Carnley, Dave [mailto:dcarnley@a...]
|Sent: Thursday, November 21, 2002 7:20 AM
|To: Access
|Subject: [access] RE: Insert Into SQL command and autonumbers
|
|
|No, I guess not... I'm not sure why I assumed he was using a SQL
|back-end :/
|
|-----Original Message-----
|From: Leo Scott [mailto:leoscott@c...]
|Sent: Wednesday, November 20, 2002 5:10 PM
|To: Access
|Subject: [access] RE: Insert Into SQL command and autonumbers
|
|
|If he's doing this against a Access database is @@Identity even supported?
|
||-----Original Message-----
||From: Carnley, Dave [mailto:dcarnley@a...]
||Sent: Wednesday, November 20, 2002 2:55 PM
||To: Access
||Subject: [access] Insert Into SQL command and autonumbers
||
||
||SQL provides a value @@IDENTITY. I do all inserts from stored procedures
||and from the S.P. I return that value using "SELECT @@IDENTITY" directly
||after the insert statement. That way my sp produces a recordset:
||
||set rs = adoconnection.execute ("sp_ins_table name='joe'...")
||NewID = rs.fields(1)
||
||I don't know if @@IDENTITY is available to VBA connection...
||
||
||
||
||-----Original Message-----
||From: rickdolson@y... [mailto:rickdolson@y...]
||Sent: Wednesday, November 20, 2002 4:27 PM
||To: Access
||Subject: [access] Insert Into SQL command and autonumbers
||
||
||I am trying to write some code in Access 2000 where when I press a button,
||I use a the "Insert Into" sql command to insert records into a table.
||However, I am having a lot of difficulty because the table has an
||autonumber field.
||
||How do you write the sql command to take into account this autonumber?
||I'd like the code to insert the records into the table, and the autonumber
||to be filled in automatically, or, I need to know how to write the code to
||pick the next autonumber to be added with the data.
||
||Thanks for any help you can give me.
||
|
|
|
Message #7 by "Carnley, Dave" <dcarnley@a...> on Thu, 21 Nov 2002 10:29:56 -0600
|
|
LOL you too... however in my case it is prolly due to careful selection of
which posts to reply to ;)
-----Original Message-----
From: Leo Scott [mailto:leoscott@c...]
Sent: Thursday, November 21, 2002 10:15 AM
To: Access
Subject: [access] RE: Insert Into SQL command and autonumbers
Usually, you are soooo good I dare not touch one of your posts. :-)
|-----Original Message-----
|From: Carnley, Dave [mailto:dcarnley@a...]
|Sent: Thursday, November 21, 2002 7:20 AM
|To: Access
|Subject: [access] RE: Insert Into SQL command and autonumbers
|
|
|No, I guess not... I'm not sure why I assumed he was using a SQL
|back-end :/
|
|-----Original Message-----
|From: Leo Scott [mailto:leoscott@c...]
|Sent: Wednesday, November 20, 2002 5:10 PM
|To: Access
|Subject: [access] RE: Insert Into SQL command and autonumbers
|
|
|If he's doing this against a Access database is @@Identity even supported?
|
||-----Original Message-----
||From: Carnley, Dave [mailto:dcarnley@a...]
||Sent: Wednesday, November 20, 2002 2:55 PM
||To: Access
||Subject: [access] Insert Into SQL command and autonumbers
||
||
||SQL provides a value @@IDENTITY. I do all inserts from stored procedures
||and from the S.P. I return that value using "SELECT @@IDENTITY" directly
||after the insert statement. That way my sp produces a recordset:
||
||set rs = adoconnection.execute ("sp_ins_table name='joe'...")
||NewID = rs.fields(1)
||
||I don't know if @@IDENTITY is available to VBA connection...
||
||
||
||
||-----Original Message-----
||From: rickdolson@y... [mailto:rickdolson@y...]
||Sent: Wednesday, November 20, 2002 4:27 PM
||To: Access
||Subject: [access] Insert Into SQL command and autonumbers
||
||
||I am trying to write some code in Access 2000 where when I press a button,
||I use a the "Insert Into" sql command to insert records into a table.
||However, I am having a lot of difficulty because the table has an
||autonumber field.
||
||How do you write the sql command to take into account this autonumber?
||I'd like the code to insert the records into the table, and the autonumber
||to be filled in automatically, or, I need to know how to write the code to
||pick the next autonumber to be added with the data.
||
||Thanks for any help you can give me.
||
|
|
|
|
|
|
View Cart
