使用MS访问完成此项目。
我试图通过删除对adodb.command对象的需求来简化我的ADODB代码。有两个要求,需要使用参数,并且需要检索受影响的记录(以确认SQL正确执行)。
我试图使用的语法在代码块中记录的文章中提到。
。{connection object}。[QUERY}的名称}] {参数1,...,参数n [,记录set object]}
>cn。[test_adodb_connection] 204,date&"&time(),rs
Sub TEST_ADODB_Connection()
'https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx
'Using ADODB without the use of .Command
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim lngRecordsAffected As Long
Set cn = CurrentProject.Connection
'TEST_ADODB_Connection Query
'INSERT INTO tbl_Log ( LogID_Orig, LogMessage )
'SELECT [NewLogID] AS _LogID, [NewLogMessage] AS _LogMessage;
Set rs = New ADODB.Recordset
cn.[TEST_ADODB_Connection] 204, Date & " " & Time(), rs
lngRecordsAffected = rs.RecordCount 'Error 3704 - no records returned
'so this is expected, but how do we
'get records affected by the update query?
Debug.Print lngRecordsAffected
End Sub
更新
包括试图简化的原始代码。
.command对象确实提供了我想要的功能,但是如果可行的话,我正在寻找一种替代方法。
文章(https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx提供了一个示例,其中可以使用参数可以执行.connection对象。我正在尝试扩展该示例并获得受影响的记录。
Sub TEST_ADODB_Command()
Dim cm As ADODB.Command
Dim rs As ADODB.Recordset
Dim iLogID_Auto As Integer
Dim strLogMessage As String
Dim lngRecordsAffected As Long
Set cm = New ADODB.Command
iLogID_Auto = 204
strLogMessage = Date & " " & Time
With cm
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "TEST_ADODB_Connection"
.CommandType = adCmdStoredProc
.NamedParameters = True ' does not work in access
.Parameters.Append .CreateParameter("[NewLogID]", adInteger, adParamInput, , iLogID_Auto)
.Parameters.Append .CreateParameter("[NewLogMessage]", adVarChar, adParamInput, 2147483647, strLogMessage)
Set rs = .Execute(lngRecordsAffected)
Debug.Print lngRecordsAffected
End With
Set rs = Nothing
Set cm = Nothing
End Sub
谢谢您的评论。我相信我已经设计了我正在寻找的东西。
两个点
-
adodb.Command如果要使用单个.Execute插入/更新和检索记录计数,则需要命令。可以在互联网上找到其中的示例,包括我在"更新"部分下的原始帖子。
-
如果您有插入/更新查询和选择查询,则不需要命令。我找不到这种方法的示例。以下是我想到的一个例子。
高级概述
- 执行插入/更新查询。插入/更新将不会使用一行方法返回记录集。
- 执行选择查询。这将返回记录集,但是,我无法像我认为的那样获得.count方法的工作。
-
tlemaster的建议链接在答案部分提供了工作。周围的工作是修改选择查询以对结果进行分组,并使用计数(*)返回计数。然后使用返回值而不是.count方法。
Sub TEST_ADODB_Connection() 'https://technet.microsoft.com/en-us/library/aa496035(v=sql.80).aspx 'Using ADODB without the use of .Command and .Parameters Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim lngRecordsAffected As Long Dim strDateTime As String Dim lngID As Long Set cn = CurrentProject.Connection strDateTime = Date & " " & Time() lngID = 204 'random number for example purpose 'TEST_ADODB_Connection INSERT Query 'INSERT INTO tbl_Log ( LogID_Orig, LogMessage ) 'SELECT [NewLogID] AS _NewLogID, [NewLogMessage] AS _LogMessage; 'This line will execute the query with the given parameters 'NOTE: Be sure to have the parameters in the correct order cn.[TEST_ADODB_Connection] lngID, strDateTime 'TEST_ADODB_Select 'SELECT Count(tbl_Log.LogID_Orig) AS recordCount 'FROM tbl_Log 'WHERE tbl_Log.LogID_Orig=[_LogID] AND tbl_Log.LogMessage=[_LogMessage]; 'Must initilize recordset object Set rs = New ADODB.Recordset 'This line will execute the query with given parameters and store 'the returning records into the recordset object (rs) 'NOTE: Again, be sure the parameters are in the correct order 'NOTE: the recordset object is always the last argument cn.[TEST_ADODB_Select] lngID, strDateTime, rs 'unable to directly utilize the .Count method of recordset 'workaround and more optimal solution is to write the SQL 'to return a count using grouping and Count(*) - see SQL above lngRecordsAffected = rs("recordCount").Value 'Close recordset object rs.Close Debug.Print lngRecordsAffected End Sub