Seems a common enough problem this, but most solutions refer to concatenating multiple SQL commands, something which I believe can't be done with ADO/VBA (I'll be glad to be shown wrong in this regard however).
这似乎是一个常见的问题,但是大多数解决方案都是指连接多个SQL命令,我相信这是ADO / VBA无法做到的(但我很高兴在这方面显示错误)。
I currently insert my new record then run a select query using (I hope) enough fields to guarantee that only the newly inserted record can be returned. My databases are rarely accessed by more than one person at a time (negligible risk of another insert happening between queries) and due to the structure of the tables, identifying the new record is normally pretty easy.
我当前插入我的新记录然后运行一个选择查询使用(我希望)足够的字段,以保证只返回新插入的记录。我的数据库很少被一个人一次访问(在查询之间发生另一次插入的风险可以忽略不计),并且由于表的结构,识别新记录通常很容易。
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.
我现在正在尝试更新一个没有太多唯一性范围的表,而不是人工主键。这意味着存在新记录可能不是唯一的风险,并且我不愿意添加字段以强制唯一性。
What's the best way to insert a record into an Access table then query the new primary key from Excel in this situation?
在这种情况下,将记录插入Access表然后从Excel查询新主键的最佳方法是什么?
Thanks for the replies. I have tried to get @@IDENTITY
working, but this always returns 0 using the code below.
谢谢你的回复。我试图让@@ IDENTITY工作,但这总是使用下面的代码返回0。
Private Sub getIdentityTest()
Dim myRecordset As New ADODB.Recordset
Dim SQL As String, SQL2 As String
SQL = "INSERT INTO tblTasks (discipline,task,owner,unit,minutes) VALUES (""testDisc3-3"",""testTask"",""testOwner"",""testUnit"",1);"
SQL2 = "SELECT @@identity AS NewID FROM tblTasks;"
If databaseConnection Is Nothing Then
createDBConnection
End If
With databaseConnection
.Open dbConnectionString
.Execute (SQL)
.Close
End With
myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly
Debug.Print myRecordset.Fields("NewID")
myRecordset.Close
Set myRecordset = Nothing
End Sub
Anything stand out being responsible?
有什么突出的责任吗?
However, given the caveats helpfully supplied by Renaud (below) there seems nearly as much risk with using @@IDENTITY
as with any other method, so I've resorted to using SELECT MAX
for now. For future reference though I would be interested to see what is wrong with my attempt above.
但是,考虑到Renaud帮助提供的警告(下图),使用@@ IDENTITY与使用任何其他方法的风险几乎相同,所以我现在使用SELECT MAX。为了将来参考,虽然我有兴趣看看我上面的尝试有什么问题。
12
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).
如果您使用AutoIncrement作为主键,那么您具有唯一性,您可以使用SELECT @@ Identity;获取最后一个自动生成ID的值(请参阅下面的警告)。
If you are not using autoincrement, and you are inserting the records from Access but you want to retrieve the last one from Excel:
如果您没有使用自动增量,并且您要从Access插入记录但是想从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:
或者,如果排序你的主要字段不会给你最后一个,你需要添加一个DateTime字段(比如InsertedDate)并在每次在该表中创建一个新记录时保存当前日期和时间,这样你就可以得到最后一个像这样:
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:
在任何一种情况下,我认为您会发现添加AutoIncrement主键更容易处理:
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 the Max()
.
这将使您更容易选择最新的记录,使用@@ Identity或通过主键排序或获取Max()。
From Excel
来自Excel
To get the data into Excel, you have a couple of choices:
要将数据导入Excel,您有以下几种选择:
create a data link using a query, so you can use the result directly in a Cell or a range.
使用查询创建数据链接,因此您可以直接在Cell或范围中使用结果。
query from VBA:
来自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
关于@@ Identity的注意事项
You have to be careful of the caveats when using @@Identity
in standard Access databases(*):
在标准Access数据库(*)中使用@@ Identity时,您必须小心注意事项:
It only works with AutoIncrement Identity fields.
它仅适用于AutoIncrement Identity字段。
It's only available if you use ADO and run SELECT @@IDENTITY;
它仅在您使用ADO并运行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.
它返回最新使用的计数器,但这适用于所有表。你不能用它来返回MS Access中特定表的计数器(据我所知,如果你使用FROM mytable指定一个表,它就会被忽略)。简而言之,返回的值可能与您期望的值完全不同。
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.
您必须在INSERT之后直接查询它,以最大限度地降低获得错误答案的风险。这意味着,如果您一次插入数据并需要在另一个时间(或其他地方)获取最后一个ID,则无法使用。
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.
最后但并非最不重要的是,仅当通过编程代码插入记录时才设置变量。这意味着通过用户界面添加了记录,将不会设置@@ IDENTITY。
(*): 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.
(*):为了清楚起见,@@ IDENTITY表现不同,并且以更具预测性的方式,如果您对数据库使用ANSI-92 SQL模式。但问题是ANSI 92与Access支持的ANSI 89风格略有不同,并且当Access用作前端时,旨在提高与SQL Server的兼容性。
7
If the artificial key is an autonumber, you can use @@identity.
如果人工密钥是自动编号,则可以使用@@ identity。
Note that with both these examples, the transaction is isolated from other events, so the identity returned is the one just inserted. You can test this by pausing the code at Debug.Print db.RecordsAffected or Debug.Print lngRecs and inserting a record manually into Table1, continue the code and note that the identity returned is not that of the record inserted manually, but of the previous record inserted by code.
请注意,对于这两个示例,事务与其他事件隔离,因此返回的标识是刚刚插入的标识。您可以通过暂停Debug.Print db.RecordsAffected或Debug.Print lngRecs中的代码并手动将记录插入Table1来测试这一点,继续代码并注意返回的标识不是手动插入的记录,而是之前的记录由代码插入。
DAO Example
DAO示例
'Reference: Microsoft DAO 3.6 Object Library '
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
db.Execute ("INSERT INTO table1 (field1, Crdate ) " _
& "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)")
Debug.Print db.RecordsAffected
Set rs = db.OpenRecordset("SELECT @@identity AS NewID FROM table1")
Debug.Print rs.Fields("NewID")
ADO Example
ADO示例
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
cn.Execute ("INSERT INTO table1 (field1, Crdate ) " _
& "VALUES ( 46, #" & Format(Date, "yyyy/mm/dd") & "#)"), lngRecs
Debug.Print lngRecs
rs.Open "SELECT @@identity AS NewID FROM table1", cn
Debug.Print rs.Fields("NewID")
3
Re: "I have tried to get @@IDENTITY working, but this always returns 0 using the code below."
回复:“我试图让@@ IDENTITY工作,但这总是使用下面的代码返回0。”
Your code sends SQL
and SQL2
through different connection objects. I don't think @@identity
will return anything other than zero unless you ask from the same connection where you executed your INSERT
statement.
您的代码通过不同的连接对象发送SQL和SQL2。我不认为@@ identity将返回除零之外的任何内容,除非您从执行INSERT语句的同一连接询问。
Try changing this:
尝试改变这个:
myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly
to:
至:
myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly
0
Here's my solution that does not use @@index or MAX.
这是我的解决方案,不使用@@ index或MAX。
Const cOnnectionString= "Provider=SQLOLEDB; Data Source=SomeSource; Initial Catalog=SomeDB; User Id=YouIDHere; Password=YourPassword"
Const RecordsSQL = "SELECT * FROM ThatOneTable"
Private Sub InsertRecordAndGetID()
Set cOnnection= New ADODB.connection
connection.cOnnectionString= connectionString
connection.Open
Set recordset = New ADODB.recordset
recordset.Open SQL, connection, adOpenKeyset, adLockOptimistic
With recordset
.AddNew
!Field1 = Value1
!Field2 = Value2
End With
recordset.MoveLast
ID = recordset.Fields("id")
End Sub
Enjoy!
请享用!
0
Try following macro code.First add a command button to the sheet from the control box and paste following codes in the code window
尝试以下宏代码。首先从控件框向工作表添加一个命令按钮,然后在代码窗口中粘贴以下代码
Private Sub CommandButton1_Click()
MsgBox GetLastPrimaryKey
End Sub
Private Function GetLastPrimaryKey() As String
Dim con As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
con = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myaccess.mdb;Persist Security Info=False"
sql = "SELECT MAX(id) FROM tblMyTable"
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open con
rs.Open sql, cn, 3, 3, 1
If rs.RecordCount <> 0 Then
GetLastPrimaryKey = rs.Fields(0).Value
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function
0
8 years late to the party... The problem you are having is that you are using dbConnectionString to create a new connection. @@identity is specific to the connection you are using.
聚会迟到8年......您遇到的问题是您正在使用dbConnectionString创建新连接。 @@ identity特定于您正在使用的连接。
First, don't close the original connection
首先,不要关闭原始连接
'.Close
replace
更换
myRecordset.Open SQL2, dbConnectionString, adOpenStatic, adLockReadOnly
with the connection you previously used for the insert
使用先前用于插入的连接
myRecordset.Open SQL2, databaseConnection, adOpenStatic, adLockReadOnly
and you'd have been all set. In fact, you don't even need to specify the table:
而且你已经全部准备好了。实际上,您甚至不需要指定表:
SQL2 = "SELECT @@identity AS NewID"