How to get id of newly inserted record using Excel VBA?
About your question:
I'm now trying to update a table that
does not have much scope for
uniqueness, other than in the
artificial primary key. This means
there is a risk that the new record
may not be unique, and I'm loathe to
add a field just to force uniqueness.
If you are using an AutoIncrement for your primary key, then you have uniqueness and you could use SELECT @@Identity;
to get the value of the last autogenerated ID (see caveats below).
If you are not using autoincrement, and you are inserting the records from Access but you want to retrieve the last one from Excel:
make sure your primary key is sortable, so you can get the last one using a query like either of these:
SELECT MAX(MyPrimaryField) FROM MyTable;
SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY MyPrimaryField DESC;or, if sorting your primary field wouldn't give you the last one, you would need to add a DateTime field (say
InsertedDate
) and save the current date and time every time you create a new record in that table so you could get the last one like this:SELECT TOP 1 MyPrimaryField FROM MyTable ORDER BY InsertedDate DESC;
In either of these cases, I think you would find adding an AutoIncrement primary key as being a lot easier to deal with:
It's not going to cost you much
It's going to guarantee you uniqueness of your records without having to think about it
It's going to make it easier for you to pick the most recent record, either using
@@Identity
or through sorting by the primary key or getting theMax()
.
From Excel
To get the data into Excel, you have a couple of choices:
create a data link using a query, so you can use the result directly in a Cell or a range.
query from VBA:
Sub GetLastPrimaryKey(PrimaryField as string, Table as string) as variant
Dim con As String
Dim rs As ADODB.Recordset
Dim sql As String
con = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= ; C:\myDatabase.accdb"
sql = "SELECT MAX([" & PrimaryField & "]) FROM [" & MyTable & "];"
Set rs = New ADODB.Recordset
rs.Open sql, con, adOpenStatic, adLockReadOnly
GetLastPrimaryKey = rs.Fields(0).Value
rs.Close
Set rs = Nothing
End Sub
Note about @@Identity
You have to be careful of the caveats when using @@Identity
in standard Access databases(*):
It only works with AutoIncrement Identity fields.
It's only available if you use ADO and run
SELECT @@IDENTITY;
It returns the latest used counter, but that's for all tables. You can't use it to return the counter for a specific table in MS Access (as far as I know, if you specify a table using
FROM mytable
, it just gets ignored).
In short, the value returned may not be at all the one you expect.You must query it straight after an
INSERT
to minimize the risk of getting a wrong answer.
That means that if you are inserting your data at one time and need to get the last ID at another time (or another place), it won't work.Last but not least, the variable is set only when records are inserted through programming code.
This means that is the record was added through the user interface,@@IDENTITY
will not be set.
(*): just to be clear, @@IDENTITY
behaves differently, and in a more predictive way, if you use ANSI-92 SQL mode for your database.
The issue though is that ANSI 92 has a slightly different syntax than
the ANSI 89 flavour supported by Access and is meant to increase compatibility with SQL Server when Access is used as a front end.
Autonumber value of last inserted row - MS Access / VBA
If DAO
use
RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName
If ADO
use
cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value
cn
being a valid ADO connection, @@Identity
will return the lastIdentity
(Autonumber) inserted on this connection.
Note that @@Identity
might be troublesome because the last generated value may not be the one you are interested in. For the Access database engine, consider a VIEW
that joins two tables, both of which have the IDENTITY
property, and you INSERT INTO
the VIEW
. For SQL Server, consider if there are triggers that in turn insert records into another table that also has the IDENTITY
property.
BTW DMax
would not work as if someone else inserts a record just after you've inserted one but before your Dmax
function finishes excecuting, then you would get their record.
How to know the ID of the most recently added record?
Firstly, you should be aware that this method of obtaining the autonumber value for the most recently added record only works reliably when evaluated within the scope of the same database workspace which created the record (when using DAO) or the same database connection (when using ADO). Evaluated independently of such connection, the statement will merely yield 0
.
Example
Say we have a table called Table1
with two fields:
Table1
+------------+--------+
| ID | Field1 |
+------------+--------+
| AutoNumber | Text |
+------------+--------+
We can create a new record using the following code:
With CurrentDb
.Execute "insert into table1 (field1) values ('abc')"
End With
If we want to find the value assigned to the AutoNumber field ID
, we can then do this:
With CurrentDb
.Execute "insert into table1 (field1) values ('abc')"
Debug.Print .OpenRecordset("select @@identity")(0)
End With
What is @@IDENTITY
?
Note that the SQL statement used in the above example is independent of any one particular table or field (there is no from
clause), as the @@identity
column merely refers to the last autonumber value issued (to any record within any table) for the current database workspace (DAO)/connection (ADO).
This T-SQL documentation may help with your understanding, but note that this applies to SQL Server, not MS Access, and so whilst this provides a description of the purpose of @@identity
, not everything will translate directly to MS Access.
How Does .OpenRecordset("...")(0)
Work?
As for the use of (0)
directly following the .OpenRecordset
method, this simply shorthand for accessing the first item in the Fields
collection (which is the default member of the Recordset class), and is therefore equivalent to:
.OpenRecordset("select @@identity").Fields(0)
How to get the last record id of a form?
There is a function DMax
that will grab the highest number.
Dim lastID As Integer
lastID = DMax("IDField","YourTable")
' or = DMax("IDField","YourTable","WhenField=Value")
MsgBox lastID
The other Domain functions are:
- DAvg
- DCount
- DFirst
- DLast
- DLookup
- DMin
- DStDev
- DStDevP
- DSum
- DVar
- DVarP
Check with your friendly F1 key for more info
Retrieving last inserted ID from MS Access database
Assuming you have an auto increment column in the Tournaments table, you can do a "SELECT @@IDENTITY" to get the id of the last inserted record.
BTW, is the SanctioningIDTxt.Text unique? If so, can't you use that?
Getting the newly added AutoID of MySQL using VBA in MS Access
So my answer comes from both ComputerVersteher and User2834566 info.
I had to run two queries, both as passthrough and running on the same db instance to make sure it returned the right value:
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rst As DAO.Recordset
strSQL = "INSERT INTO medstome_masterdb.tblChemists (chemName) VALUES ('newchem')"
strGETID = "SELECT @@IDENTITY"
Set db = CurrentDb
With db
Set qdf = db.CreateQueryDef("")
With qdf
.ReturnsRecords = False
.Connect = oCon
.SQL = strSQL
.Execute
End With
With qdf
.ReturnsRecords = True
.Connect = oCon
.SQL = strGETID
Set rst = .OpenRecordset(dbOpenSnapshot)
gPxID = rst(0)
Debug.Print gPxID
End With
End With
This works perfectly!
Thanks to the guys responding and helping
Create unique id for each SQL record in VBA
You could move your RecordID logic inside the loop like this:
For Each row In rng.Rows
arr(0, 0) = arr(0, 0) + 1
query2 = "INSERT INTO tbl_eInvoice_Main (RecordID) values (" & arr(0, 0) & ")"
con.Execute query2
Next row
However, this approach won't work well if you have multiple users. Just something to keep in mind.
Related Topics
I Am Trying to Copy a File, But Getting Error Message
Oracle 11G: Unpivot Multiple Columns and Include Column Name
Oracle Text Escaping with Curly Braces and Wildcards
Why Google's Bigtable Referred as a Nosql Database
How to Search for Names with Apostrophe in SQL Server
Why Is There a Scan on My Clustered Index
Simple Db2 Query for Connection Validation
Datareader.Getfieldtype Returned Null
Create a New Db User in SQL Server 2005
Null Value for Int in Update Statement
Generated Excel from Ssis But Getting Quote in Every Column
Inserting a String with Double Quotes into a Table
Oracle SQL Return True If Exists Question
Delete ... from ... Where ... In
How to Perform a Simple String Mapping as Part of a T-Sql Select