如何从访问表中查找下一个ID自动编号并在Excel用户表单中显示该编号?

Question

我想从访问表中获取下一个 ID 自动编号，并将其显示在 excel 用户窗体的文本框中。我找到了这段代码，我已经玩过了它，但我一无所获，完全迷失了。如果有人能指出我解决问题的正确方向，我将不胜感激。

Dim con As Object: Set con = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
Dim sql As String
connectionstring = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
connectionstring = connectionstring & "DATA Source=C:UsersMyPcDesktopDatabase1.accdb;"
con.Open connectionstring
sql = "select max(ID) as maxvalue from TableOne"
con.Execute sql
NextValue = (maxvalue) + 1
MsgBox NextValue
userform1.textbox2.value = NextValue
rs.Close
Set rs = Nothing

Answer 1

执行是针对操作 SQL (INSERT、UPDATE、DELETE( 而不是 SELECT。使用 SELECT 打开记录集对象，然后从记录集中读取数据。

Dim con As Object: Set con = CreateObject("ADODB.Connection")
Dim rs As Object: Set rs = CreateObject("ADODB.Recordset")
Dim sql As String
connectionstring = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
connectionstring = connectionstring & "DATA Source=C:UsersMyPcDesktopDatabase1.accdb;"
con.Open connectionstring
sql = "select max(ID) as maxvalue from TableOne"
rs.Open sql, con, adOpenStatic, adLockReadOnly 
NextValue = (rs!maxvalue) + 1
MsgBox NextValue
userform1.textbox2.value = NextValue
rs.Close
Set rs = Nothing

由于只需要一个值，请考虑替代值：

Dim appAccess As Object
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase ("C:UsersMyPcDesktopDatabase1Umpires.accdb")
userform1.textbox2.value = appAccess.DMax("ID", "TableOne")