SQL Server后端断开连接 [英] SQL Server Backend disconnects

问题描述

我在Access 2007中有一个大型FE应用程序，现在有一个SQL Server BE（最初有一个A2007 BE）。我的用户正在经历这样的情况：他们让表格在长时间（不到60分钟）内保持不变，并且表格似乎"失去联系"。
到BE。也就是说，他们得到奇怪的错误，表明表单的记录集已经丢失了，或者组合框丢失了它的行源。我需要更改哪些设置才能确保不会发生这种情况？我是否需要一个维持"连接"的小程序？
每隔几分钟到一个小桌面？

---

Jim

I have a large FE app in Access 2007 that now has a SQL Server BE (originally had an A2007 BE). My users are experiencing situations where they leave forms open an untouched for lengthy (under 60 minutes) times, and the forms seem to "lose connection" to the BE. That is, they get odd errors indicating the form's recordset has lost its way, or a combo box has lost its row source. What setting do I need to change to ensure this doesn't happen? Do I need to just have a little routine that maintains "connection" to a little table every few minutes?

---

Jim

推荐答案

如果您使用ODBC连接 - 它可能是超时问题。 我会使用ADO来处理ADO.Net断开连接的记录集范例，以解决这个问题。 使用ADO，您可以将所需的数据从sql server表中拉入mdb中的本地
表（或者他们称之为A2007的任何表），其中这些本地表就像临时表一样。  在.Net中，等效的是将数据从服务器提取到内存表中 - 在Access中，表是将数据存储在
磁盘上的物理表。 因此，您必须确保在不需要时从这些本地表中删除数据。 但是这种技术可以解决您遇到的超时问题。 以下是如何在代码中使用ADO的示例：

If you are using ODBC connections - it may be a timeout issue.  I would use ADO which works kind of the the ADO.Net disconnected recordset paradigm to get around this issue.  With ADO you pull the data you need from the sql server tables into local table in the mdb (or whatever they call it for A2007) where these local tables act like temp tables.   In .Net the equivalent is pulling data from the server into memory tables - in Access the tables are physical tables that store the data on the disk.  So you have to make sure to remove the data from these local tables when you don't need it.  But this technique is a workaround for the timeout issue you are experiencing.  Here is a sample of how to use ADO in code:

Sub readFromSqlSvr（）

   Dim cmd As New ADODB.Command，RS As New ADODB.Recordset，RSdao As DAO.Recordset，i As Integer

   ' - 确保有一个对Microsoft ActiveX Data Objects 2的引用。？ （5或更高）Libary in Tools / References

   cmd.ActiveConnection =" Provider = SQLOLEDB;数据源= yourServer;数据库= yourDB; Trusted_Connection =是"
$
   cmd.ActiveConnection.CursorLocation = adUseClient

   cmd.CommandType = adCmdText

   cmd.CommandText =" Select * from yourTbl"

  设置RS = cmd.Execute

   DoCmd.RunSql"删除*来自someTmpTbl"  ' - 首先清楚当地餐桌

  设置RSdao = CurrentDB.OpenRecordset（" someTmpTbl"） 

  请不要RS.EOF为
      RSdao.AddNew为
     对于i = 0 To RSdao.Fields.Count - 1

         RSdao（i）= RS（i）

     下一个

      RSdao.Update

      RS.MoveNext

  循环

   cmd.ActiveConnection.Close

  

End Sub

Sub readFromSqlSvr()
   Dim cmd As New ADODB.Command, RS As New ADODB.Recordset, RSdao As DAO.Recordset, i As Integer
   '--make sure to have a Reference to the Microsoft ActiveX Data Objects 2.? (5 or greater) Libary in Tools/References
   cmd.ActiveConnection = "Provider=SQLOLEDB; Data Source=yourServer;Database=yourDB;Trusted_Connection=Yes"
   cmd.ActiveConnection.CursorLocation = adUseClient
   cmd.CommandType = adCmdText
   cmd.CommandText = "Select * from yourTbl"
   Set RS = cmd.Execute
   DoCmd.RunSql "Delete * From someTmpTbl"  '--clear local table first
   set RSdao = CurrentDB.OpenRecordset("someTmpTbl") 
   Do While Not RS.EOF
      RSdao.AddNew
      For i = 0 To RSdao.Fields.Count - 1
         RSdao(i) = RS(i)
      Next
      RSdao.Update
      RS.MoveNext
   Loop
   cmd.ActiveConnection.Close
  
End Sub

 

这篇关于SQL Server后端断开连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！