如果不是 RS.EOF而不是RS.然后为 ADODB 编写 BOF 而不是当前的 DAO 格式

Question

我目前使用If Not RS。EOF而不是RS。BOF是一个DAO记录集，但我不能在新的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

从本质上讲，我已经开始用ADODB编写代码。 然而，在研究如果不是 RS。网上的EOF话题为ADODB，我完全没有成功。是否有人了解利用率 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

Answer 1

在 DAO 的情况下，您的代码使用 （DAO） 记录集对象并避免显式 SQL 语句

;在 ADO 情况下，您使用显式 SQL 语句并避免使用 （ADO） 记录集对象。因此，对您的问题的简短回答是：使用 ADO 记录集对象，您将拥有 BOF 和 EOF 属性，就像 DAO （http://msdn.microsoft.com/en-us/library/windows/desktop/ms675787%28v=vs.85%29.aspx） 一样。顺便说一句，在 ADO 情况下，您也使用后期绑定，我建议您改用早期绑定（即，添加对 ADO 类型库的引用并使用强非弱类型对象变量）。

也就是说，在 DAO Recordset 的情况下，您通常通过调用 Database 对象的 OpenRecordset 方法来获取它的实例;相反，在调用其 Open 方法之前直接实例化 ADO 版本，通常传入 ADO Connection对象（ADO Connection大致对应于 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

Answer 2

我建议您对此进行测试，我已经编译了，但没有运行它，因为我没有在数据库中创建这样的表：

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，因为我们使用直接更新。

Answer 3

这是我的答案：

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

Answer 4

"

如果不是Rs.EOF而不是Rs.BOF那么"是一种查看记录集是否为空的方法。仅当记录集为空时，Rs.BOF 和 Rs.EOF 才能为真。