如何在一个子系统中从Excel查询和更新Access中的表

Question

我在Excel中有一个带有四个文本框的Userform。每个文本框对应Access中Table1的一个字段。

我想从Access中的一个表中返回MAX行ID值，在该值上加1，然后在用户窗体的文本框中显示该值。

在其他3个文本框中输入值后，我想在Access中将数据导出到Table1。

这一切能在同一个子例程中完成吗？请帮助我将sql语句合并到代码中的正确位置。

谢谢

Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim x As Long, i As Long
Dim PrimaryField As String
Dim MyTable As String
Dim GetLastPrimaryKey As Variant
PrimaryField = "ID"
MyTable = "Table1"

'Erro handler
On Error GoTo errHandler:
'dbPath = ActiveSheet.Range("H500").Value
dbPath = "H:AnnieFile.accdb"
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
sql = "SELECT MAX([" & PrimaryField & "]) FROM [" & MyTable & "];"
'two primary providers used in ADO SQLOLEDB —-Microsoft.JET.OLEDB.4.0 —-Microsoft.ACE.OLEDB.12.0
'Object Linking and Embedding, Database
'ADO library is equipped with a class named Recordset
Set rst = New ADODB.Recordset 'assign memory to the recordset
'ConnectionString Open '—-5 aguments—-
'Source, ActiveConnection, CursorType, LockType, Options
rst.Open Source:="Table1", ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdTable
'rst.Open sql, cnn

GetLastPrimaryKey = rst.Fields(0).Value
MsgBox (GetLastPrimaryKey)
GetLastPrimaryKey = Arec1.Value
'you now have the recordset object
'alternative code
With rst
    .AddNew
    .Fields("ID").Value = Arec1
    .Fields("patient").Value = Arec2
    .Fields("test").Value = Arec3
    .Fields("CommentTxt").Value = Arec4
    .Update
End With

'clear the userform values
For x = 1 To 4
UserForm1.Controls("Arec" & x).Value = ""
Next
'add the next user ID
'Me.Arec1 = Sheet1.Range("J3").Value
' Close the connection
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
'commuinicate with the user
MsgBox " The data has been successfully sent to the access database"
On Error GoTo 0
Exit Sub
errHandler:
Set rst = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAdd"

End Sub

Answer 1

考虑打开一个新的记录集。当然，更改Excel用户表单文本框名称（此处为占位符）：

Set maxIDrst = New ADODB.Recordset
sql = "SELECT MAX([" & PrimaryField & "]) + 1 as MaxIDPlusOne FROM [" & MyTable & "];"
maxIDrst.Open sql, conn
UserForm1.Controls("Arec5").Value = maxIDrst!MaxIDPlusOne
maxIDrst.Close
...
Set maxIDrst = nothing