链接到断开连接的ADODB.Recordset的访问表单:保存更改 [英] Access form linked to disconnected ADODB.Recordset: save changes

问题描述

我正在尝试建立一个表单，以使用断开连接的ADODB.Recordset作为其源.
我遇到的问题是，在关闭表单并对提示回答是"后，更改不被保存到原始Access表中.我想念什么?

I am trying to set up a form to use a disconnected ADODB.Recordset as its source.
The issue I have is that changes are not saved into the original Access table upon closing the form and replying "Yes" to the prompt. What am I missing ?

注意:请不要告诉我该方法没有用，它只是一个带有本地表的POC，我计划以后再尝试使用更遥远"的记录集.

Note: Please don't tell me the method is useless, it's just a POC with a local table, I plan to try later with a more "distant" recordset.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
    Set conn = New ADODB.Connection
    conn.Open CurrentProject.Connection

    Set rs = New ADODB.Recordset
    With rs
        rs.CursorLocation = adUseClient
        rs.Open "select * from amsPor", conn, adOpenStatic, adLockBatchOptimistic
        Set rs.ActiveConnection = Nothing
    End With
    Set Me.Recordset = rs
    conn.Close
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Select Case MsgBox("Save changes ?", vbQuestion + vbYesNoCancel)
    Case vbNo
        'do nothing
    Case vbYes
        conn.Open CurrentProject.Connection
        rs.ActiveConnection = conn
        rs.UpdateBatch
        rs.Close
        conn.Close
        Set conn = Nothing
    Case vbCancel
        Cancel = True
    End Select
End Sub

---

复制步骤:

• 带一个有主键的小桌子
• 使用它生成自动表格
• 保存表格.
• 将上述代码添加到表单中，替换select子句中的表名.
• 清空表单的Record Source属性.
• 保存并关闭表单.
• 您可以打开表单并更改数据.关闭后，系统将提示您保存更改.

• Take a small table which has a primary key
• Generate an automatic form with it
• Save the form.
• Add the above code to the form, replacing the table name in the select clause.
• Empty the Record Source property of the form.
• Save and Close the form.
• You can open the form and make changes to the data. Upon close, you will be prompted for saving your changes.

编辑:我想知道问题是否出在CurrentProject.Connection吗?
在调试窗口中，键入? CurrentProject.Connection并得到以下信息:

EDIT: I wonder if the issue might be in the CurrentProject.Connection ?
In the debug window, I typed ? CurrentProject.Connection and got the following:

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=\\xxxxxx\yyyy$\Documents\AMS.accdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database=C:\Users\G828992\AppData\Roaming\Microsoft\Access\System.mdw;Jet OLEDB:Registry Path=Software\Microsoft\Office\14.0\Access\Access Connectivity Engine;Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=True;Jet OLEDB:Bypass UserInfo Validation=False

推荐答案

我来到这里寻找与您相同的答案，经过大量的谷歌搜索和反复试验后，我终于能够完全按照您的意图进行操作.我知道这是一篇过时的文章，但是我没有看到任何答案，实际上没有提供可以让您尝试做的事情的答案.我将使用您的示例，并尝试应用我必须更改和添加的内容以使其正常工作.

I came here looking for the same answer as you and after tons of googling and trial and error I finally was able to perform exactly what you are attempting to do. I understand this is an old post but I did not see any answers that actually provided an answer that would allow what you are attempting to do work. I will use your example and try and apply what I had to change and add to get it to work properly.

Dim rs As ADODB.Recordset
Dim conn As ADODB.Connection

Private Sub Form_Load()
    If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close

    Set conn = New ADODB.Connection
    conn.Open CurrentProject.Connection.ConnectionString

    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    rs.Open "select * from amsPor", conn, adOpenForwardOnly, adLockBatchOptimistic

    If Not rs Is Nothing Then
        If Not rs.ActiveConnection Is Nothing Then Set rs.ActiveConnection = Nothing
        If Not (rs.eof And rs.BOF) Then
            Set Me.Recordset = rs
        End If

        If conn.State = adStateOpen Then
            conn.Close
        End If
    End If

    Call AddNewRecord(Me.Recordset)
End Sub


Private Sub AddNewRecord(ByRef rs As ADODB.Recordset)
    On Error Resume Next 

    If Not rs Is Nothing Then
        If rs.Supports(adAddNew) Then
            rs.AddNew
            rs.Fields("FirstName").Value = "John"
            rs.Fields("LastName").Value = "Doe"

            If rs.Supports(adUpdate) Then rs.Update
        End If
    End If

    If Err.Number <> 0 Then
        Debug.Print "AddNewRecord Err Msg: " & Err.Description
        Err.Clear
    End If
End Sub


Private Sub Form_Unload(Cancel As Integer)
    Select Case MsgBox("Save changes ?", vbQuestion + vbYesNoCancel)
    Case vbYes
        Call UpdateDbWithRS(Me.Recordset)
    Case vbCancel
        Cancel = True
    Case Else
        ' Nothing.
    End Select
End Sub


Private Sub UpdateDbWithRS(ByRef rs As ADODB.Recordset)
    If Not rs Is Nothing Then
        If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close

        Set conn = New ADODB.Connection
        conn.Open CurrentProject.Connection.ConnectionString
        rs.ActiveConnection = conn

        If rs.Supports(adUpdateBatch) Then
            rs.UpdateBatch

            If Not conn Is Nothing Then
                If conn.State = adStateOpen Then conn.Close
                Set conn = Nothing
            End If
            If Not rs Is Nothing Then
                If rs.State = adStateOpen Then rs.Close
                Set rs = Nothing
            End If
        End If
    End If
End Sub

使用上面的代码，我可以添加一条记录到我的记录集，并验证它没有显示在数据库表中.然后，当我执行之前添加到 Recordset 的 Record 的 UpdateDbWithRS 时，现在被推送到我的数据库表中.

With the code above I was able to Add a Record to my Recordset and verify it did not show up in my database table. Then when I performed the UpdateDbWithRS the Record that I had added to the Recordset, previously, was now pushed to my database table.

我对您的代码所做的最大更改是将conn.Open CurrentProject.Connection更改为conn.Open CurrentProject.Connection.ConnectionString，并添加了代码If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close来修复我收到的有关已打开的连接的错误.然后，我所做的最后最大更改是将adOpenStatic的 CursorType 替换为adOpenForwardOnly.我不确定是否真的需要进行最后的更改，但是我根据在

The biggest changes I had to do with your code was changing conn.Open CurrentProject.Connection to conn.Open CurrentProject.Connection.ConnectionString, adding in the code If CurrentProject.Connection.State = adStateOpen Then CurrentProject.Connection.Close to fix the error I was receiving about the connection already being opened. Then the final biggest change I made was replacing your CursorType of adOpenStatic to adOpenForwardOnly. I am not sure if that last change is truly required but I used it based on a disconnected RecordSet example I found on this Microsoft Support Site.

这篇关于链接到断开连接的ADODB.Recordset的访问表单:保存更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！