-
Aug 26th, 2014, 10:01 AM
#1
Thread Starter
Junior Member
Retrieve Last Inserted ID in MS Access
I'm using the following method to insert a record in MS Access 2007 through VB6
vrs.addnew
vrs("col1") = "Col1"
vrs("Col2") = "col2" , etc
The first column of the table is an autoincrement column. How to retrieve the ID of the newly inserted record.
Note :
In vb.net we use "Inserted.ID" method to retrieve just after insertion.
vSqlText = "INSERT INTO Table1(Col1, Col2) OUTPUT INSERTED.ID " & _
"VALUES (" & Col1) & _ etc
-
Aug 26th, 2014, 10:20 AM
#2
Re: Retrieve Last Inserted ID in MS Access
-
Aug 26th, 2014, 10:28 AM
#3
Re: Retrieve Last Inserted ID in MS Access
like:
Code:
select max(id) as maxid from tablename
-
Aug 26th, 2014, 10:36 AM
#4
Re: Retrieve Last Inserted ID in MS Access
What you showed that you use in VB.Net is just a SQL statement and that same SQL statement should work in VB6 since VB is not what is processing the query.
In the case of the VB6 code you are using a much different method.
You could also try
Code:
"Select @@Identity"
after your update
-
Aug 26th, 2014, 10:44 AM
#5
Thread Starter
Junior Member
Re: Retrieve Last Inserted ID in MS Access
Datamiser : This is the method i came to know. But how to use it ? Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?
-
Aug 26th, 2014, 03:23 PM
#6
Re: Retrieve Last Inserted ID in MS Access
akshobhya
i suppose, what you really want to know is
what autonumber is the system going to give to that new record
have a look here it does what i think you really want
http://www.vbforums.com/showthread.p...ber-(too-slow)
edit:
it is in post #16
Last edited by IkkeEnGij; Aug 26th, 2014 at 03:29 PM.
Reason: forgot to mention the post
-
Aug 26th, 2014, 03:31 PM
#7
Re: Retrieve Last Inserted ID in MS Access
Originally Posted by Akshobhya
Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?
As far as I can recall the documentation suggests this value is cached within the Provider, separately for every Connection. I can't recall a case where assuming that has ever failed me.
I can't provide a quote though because I can't find it right now.
-
Aug 26th, 2014, 04:40 PM
#8
Re: Retrieve Last Inserted ID in MS Access
What will happen if two different people try to insert record into the same table from two different machines at the same time ?
your code will have to account for that
info:
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx
to remember:
never and never and absolutely never give a user the possibility to start an addnew/edit and then go on a coffee break
(and even more times never give him/her the possibility to do so if you choice to use pessimictic locking)
the code i gave may seem to contradict that
but remember i did not issue an update,so there never was a record added
and to make absolutely sure there would be no lock, i explicitely used optimistic locking
Last edited by IkkeEnGij; Aug 26th, 2014 at 04:43 PM.
Reason: added /edit
-
Aug 26th, 2014, 09:16 PM
#9
Addicted Member
Re: Retrieve Last Inserted ID in MS Access
Originally Posted by Akshobhya
Datamiser : This is the method i came to know. But how to use it ? Please give me some more clarification. What will happen if two different people try to insert record into the same table from two different machines at the same time ?
IN addition to DataMisers Query:
You can also use IDENT_CURRENT limited to the table specified.
Code:
SELECT IDENT_CURRENT(<TABLE NAME>) AS Identity
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
-
Aug 26th, 2014, 11:36 PM
#10
Thread Starter
Junior Member
Re: Retrieve Last Inserted ID in MS Access
Courtesy : http://www.vbforums.com/showthread.p...ord-just-added
This way it seem to be so easy.
Dim NewID As Long
With vRs
.AddNew
.Fields("Col1").Value = "cols1"
.Fields("Col2").Value = "cols2"
.Update
NewID = .Fields("ID").Value
End With
MsgBox NewID
Any drawback by this method ? Experts please share with me....
-
Aug 27th, 2014, 12:53 AM
#11
Addicted Member
Re: Retrieve Last Inserted ID in MS Access
Originally Posted by Akshobhya
Courtesy : http://www.vbforums.com/showthread.p...ord-just-added
This way it seem to be so easy.
Dim NewID As Long
With vRs
.AddNew
.Fields("Col1").Value = "cols1"
.Fields("Col2").Value = "cols2"
.Update
NewID = .Fields("ID").Value
End With
MsgBox NewID
Any drawback by this method ? Experts please share with me....
Yes, you can use that code. ^_^
Since it is an autonumber, there is no problem even if you delete the last identity.
for e.g.
ID
1
2
3
4
5 (I DELETED THIS RECORD)
next update:
1
2
3
4
6
If I had helped you...
Don't forget to mark your Inquiry as RESOLVED...
I will be glad if you can also give me some Reputation points in helping you (by Clicking Rate This Post)...
Happy VB Coding Everyone!
-
Aug 27th, 2014, 02:35 AM
#12
Re: Retrieve Last Inserted ID in MS Access
Any drawback by this method ?
the drawback is,that code assumes it will succeed in adding a new record
in a multiuser environment you can never assume an addnew/edit will succeed
you should always have an error trap active (yes,i know you'r just giving an example)
what is the difference between what you are doing, and what i did propose:
that system will give you the number after the record was added
my system will give you the number that the new record will get before it actually is added
but the sad news is:
nobody can really help you out,simply because nobody knows what you are actually doing
are you using ado or dao,what kind of recordsets are you using,what kind of locking are you using
Last edited by IkkeEnGij; Aug 31st, 2014 at 07:48 AM.
Reason: deleted part that was not exactly correct (depended on a specific version)
-
Aug 27th, 2014, 03:28 AM
#13
Re: Retrieve Last Inserted ID in MS Access
a word about autonumbers in access
in the following i assume the autonumber is a long integer
(so,it is not a replication-id,that are unique by definition)
an autonumber is nothing more than a long integer
as such it is only restricted to the range of a long integer
so if you are adding new records you can give the autonumber field any value you want
as long as it is in the range of a long integer
you can give the same number to any number of records you want
Except if there is a constraint on that autonumber field
then it has to be in the range of an integer and comply to the constraint
so if a record with autonumber 5 is deleted
that number 5 can be reused
Except if there is a constraint on that field that says the number 5 can not be used
(but then,of course,it could not have been deleted,since it could never have been added to the table)
-
Aug 27th, 2014, 05:34 AM
#14
Thread Starter
Junior Member
Re: Retrieve Last Inserted ID in MS Access
Here is my code (simple...)
Dim vSql As String
Dim vRs As New ADODB.Recordset
vSql = "Select * from TableA where 1=2"
Set vRs = New Recordset
vRs.Open vSql, ConnStr, adOpenDynamic, adLockOptimistic
with vRs
'Fld updation codes
end with
Last edited by Akshobhya; Aug 27th, 2014 at 05:38 AM.
-
Aug 29th, 2014, 08:01 AM
#15
Re: Retrieve Last Inserted ID in MS Access
Originally Posted by Akshobhya
vSql = "Select * from TableA where 1=2"
Is this thread resolved? Also, that query will never work because 1 will never equal 2.
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Aug 29th, 2014, 08:54 AM
#16
Re: Retrieve Last Inserted ID in MS Access
Originally Posted by Nightwalker83
Is this thread resolved? Also, that query will never work because 1 will never equal 2.
If you look at the other posts he is adding a record so there is nothing wrong with the query. It is intentionally returning an empty recordset which is fine even preferred if you are just wanting to add a record.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|