vba 如果不是 RS.EOF 和不是 RS.BOF，则为 ADODB 与当前的 DAO 格式编写
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19989772/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If Not RS.EOF and Not RS.BOF then written for ADODB versus current DAO format
提问by T-Rex
I currently have use of If Not RS.EOF and Not RS.BOF is a DAO Recordset, but I cannot use DAO in the new SQL backend environment. Code is as follows:
我目前使用 If Not RS.EOF 和 Not RS.BOF is a DAO Recordset，但我不能在新的 SQL 后端环境中使用 DAO。代码如下：
Function CloseSession()
'This closes the open session
Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("SELECT * FROM Tbl_LoginSessions WHERE fldLoginKey =" & LngLoginId)
If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Rs.Fields("fldLogoutEvent").Value = Now()
Rs.Update
Rs.Close
End If
Set Rs = CurrentDb.OpenRecordset("SELECT * FROM [Tbl_Users] WHERE PKUserID =" & LngUserID)
'Flag user as being logged out
If Not Rs.EOF And Not Rs.BOF Then
Rs.Edit
Rs.Fields("fldLoggedIn").Value = 0
Rs.Fields("FldComputer").Value = ""
Rs.Update
Rs.Close
End If
Set Rs = Nothing
End Function
Essentially, I have started to write the code in ADODB. However, upon researching If Not RS.EOF topic on the internet for ADODB, I was utterly unsuccessful. Does someone have knowledge on the utilization RS.EOF and RS.BOF that could be helpful in my plight to rewrite?
本质上，我已经开始在 ADODB 中编写代码。但是，在互联网上为 ADODB 研究 If Not RS.EOF 主题时，我完全没有成功。是否有人了解 RS.EOF 和 RS.BOF 的使用情况，这可能有助于我重写的困境？
Function CloseSession()
'/This closes the open session
'/Define the OLE DB connection string.
StrConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"
'/Instantiate the Connection object and open a database connection.
Set cnn = CreateObject("ADODB.Connection")
cnn.Open StrConnectionString
Dim strSQL1 As String
Dim strSQL2 As String
Dim StrLoginName As String
Dim StrComputerName As String
'/passing variables
StrComputerName = FindComputerName
strLoggedIn = "False"
'/Declaring what table you are passing the variables to
strSQL1 = "Update tTbl_LoginSessions SET fldLogoutEvent = '" & Now() & "'" & _
" WHERE fldLoginKey = " & LngLoginId
'/Declaring what table you are passing the variables to
strSQL2 = "Update tTbl_LoginUsers SET fldLoggedIn = '" & strLoggedIn & "', fldComputer = '" & StrComputerName & "'" & _
" WHERE intCPIIUserID = " & LngUserID
cnn.Execute strSQL1, , adCmdText + adExecuteNoRecords
cnn.Execute strSQL2, , adCmdText + adExecuteNoRecords
'/close connections and clean up
cnn.Close
Set cnn = Nothing
End Function
采纳答案by T-Rex
This was my answer:
这是我的回答：
Function LogMeIn(sUser As Long)
' was Function LogMeIn()
'/Go to the users table and record that the user has logged in
'/and which computer they have logged in from
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String
'/Dim sUser As Long
Dim strComputerName As String, strLoggedIn As String
'passing variables
strLoggedIn = "True"
strComputerName = FindComputerName()
'Declaring what table you are passing the variables to
strSQL = "Update dbo.tTbl_LoginUsers SET fldLoggedIn = '" & strLoggedIn & "', fldComputer = '" & strComputerName & "'" & _
" WHERE intCPIIUserID = " & sUser
Debug.Print strSQL
'connect to SQL Server
Set con = New ADODB.Connection
With con
.ConnectionString = cSQLConn
.Open
End With
'write back
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = strSQL
.CommandType = adCmdText
.Execute
'Debug.Print strSQL
End With
'close connections
con.Close
Set cmd = Nothing
Set con = Nothing
End Function
回答by Chris Rolliston
In the DAO case your code is using (DAO) Recordset objects and avoiding explicit SQL statements; in the ADO case you're using explicit SQL statements and avoiding using (ADO) Recordset objects. As such, the short answer to your question is: use ADO Recordset objects, and you will have your BOFand EOFproperties like with DAO (http://msdn.microsoft.com/en-us/library/windows/desktop/ms675787%28v=vs.85%29.aspx). As an aside, you're also using late binding in the ADO case, and I'd suggest you use early binding instead (i.e., add a reference to the ADO type library and use strongly not weakly typed object variables).
在 DAO 情况下，您的代码使用 (DAO) Recordset 对象并避免显式 SQL 语句；在 ADO 情况下，您使用显式 SQL 语句并避免使用 (ADO) Recordset 对象。因此，简短的回答你的问题是：使用ADO Recordset对象，你将有你BOF和EOF性能像DAO（http://msdn.microsoft.com/en-us/library/windows/desktop/ms675787%28v =vs.85%29.aspx）。顺便说一句，您还在 ADO 情况下使用后期绑定，我建议您改用早期绑定（即，添加对 ADO 类型库的引用并使用强类型而非弱类型对象变量）。
That said, in the DAO Recordset case you usually get an instance of it by calling a Databaseobject's OpenRecordsetmethod; in contrast, you instantiate the ADO version directly before calling its Openmethod, usually passing in an ADO Connectionobject (an ADO Connectionroughly corresponds to a DAO Database). There's also no explicit Editmethod:
也就是说，在 DAO Recordset 情况下，您通常通过调用Database对象的OpenRecordset方法来获取它的实例；相反，您在调用其Open方法之前直接实例化 ADO 版本，通常传入一个 ADOConnection对象（一个 ADOConnection大致对应于一个 DAO Database）。也没有明确的Edit方法：
Dim Connection As New ADODB.Connection, RS As New ADODB.Recordset
Connection.Open StrConnectionString
RS.Open "SELECT * FROM Tbl_LoginSessions WHERE fldLoginKey =" & LngLoginId,
Connection, adOpenForwardOnly, adLockPessimistic
If Not Rs.EOF And Not Rs.BOF Then
Rs.Fields("fldLogoutEvent").Value = Now() '!!!though see jacouh's comment
Rs.Update
Rs.Close
End If
RS.Open "SELECT * FROM [Tbl_Users] WHERE PKUserID =" & LngUserID,
Connection, adOpenForwardOnly, adLockPessimistic
'Flag user as being logged out
If Not Rs.EOF And Not Rs.BOF Then
Rs.Fields("fldLoggedIn").Value = 0
Rs.Fields("FldComputer").Value = ""
Rs.Update
Rs.Close
End If
回答by jacouh
I'll suggest you to test this, I've compiled, but not run it as I've not created such tables in db:
我建议你测试一下，我已经编译，但没有运行它，因为我没有在 db 中创建这样的表：
Function CloseSession()
'/This closes the open session
'/Define the OLE DB connection string.
Dim LngLoginId, LngUserID
Dim nDone
Dim StrConnectionString
Dim strSQL1 As String
Dim strSQL2 As String
Dim strLoggedIn As String
Dim StrLoginName As String
Dim StrComputerName As String
Dim cnn
StrConnectionString = "DRIVER=SQL Server;SERVER=dbswd0027;UID=Mickey01;PWD=Mouse02;DATABASE=Regulatory;"
'/Instantiate the Connection object and open a database connection.
Set cnn = CreateObject("ADODB.Connection")
cnn.Open StrConnectionString
'/passing variables
' StrComputerName = FindComputerName
StrComputerName = Environ("COMPUTERNAME")
LngLoginId = 58
LngUserID = 38
strLoggedIn = "False"
'/Declaring what table you are passing the variables to
strSQL1 = "UPDATE tTbl_LoginSessions" _
& " SET fldLogoutEvent = '" & Format(Now(), "yyyy-mm-ddThh:mm:ss") & "'" & _
" WHERE fldLoginKey = " & LngLoginId
'/Declaring what table you are passing the variables to
strSQL2 = "UPDATE tTbl_LoginUsers" _
& " SET fldLoggedIn = '" & strLoggedIn & "'" _
& ", fldComputer = '" & StrComputerName & "'" & _
" WHERE intCPIIUserID = " & LngUserID
cnn.Execute strSQL1, nDone
cnn.Execute strSQL2, nDone
'/close connections and clean up
cnn.Close
Set cnn = Nothing
'
End Function
RS.EOF is not used here, as we use a direct update.
此处不使用 RS.EOF，因为我们使用直接更新。
回答by Jim Snyder
"If Not Rs.EOF And Not Rs.BOF Then" is a way of seeing if the recordset is empty or not. Only when the recordset is empty can Rs.BOF and Rs.EOF both be true.
“If Not Rs.EOF And Not Rs.BOF Then”是一种查看记录集是否为空的方法。只有当记录集为空时，Rs.BOF 和 Rs.EOF 才能为真。
