将记录从一个表移动到另一个表 [英] Moving records from one table to another

问题描述

我有一个对表A执行搜索查询的表单，该表将结果输出到同一表单的子表单上.

I have a form that performs a search query on table A, which outputs the result on a subform on the same form.

我想要做的是添加一个按钮，该按钮会将子窗体上的选定记录移到表B中，从表A中删除记录(我认为从原理上讲，该记录与我想要的类似)请执行以下操作)，然后重新查询搜索，我认为可以使用me.requery这样的简单操作来完成搜索.

What I want to do is to add in a button that will move selected records on the subform into table B, deletes the records from table A (which I assume will be similar in principle to what I want to do below), then requeries the search, which I assume can be done with something as simple as me.requery.

strID = Me.Form1_subform.Form.ID.Value
strSQL = "INSERT INTO B ([a], [b], [c]) "
strSQL = strSQL + "SELECT [a], [b], [c] "
strSQL = strSQL + "From A"
strSQL = strSQL + "WHERE [ID] = " & strID
DoCmd.RunSQL strSQL

以上是我试图将记录从A插入到B中.现在，上面的代码仅对许多选定记录中的第一个执行操作(它按预期工作，但我似乎无法找出如何获取Access来识别正在选择其他记录的方法).是否有一种简单的方法可以在Access中执行上述代码的循环?目前，我只能使Access可以处理最上面的选定记录，而这并不是我真正可以使用的东西.

The above is my attempt to insert records from A into B. Right now, the above code only performs the operation on the first of many selected records (it works as expected, but I can't seem to find out how to get Access to recognize that other records are being selected). Is there a simple way of performing a loop of the above code in Access? I am currently only able to get Access to work with the topmost selected record, and that is not something I can really work with.

此外，上述代码在移入表B中时仍保留主键值(ID).有办法防止这种情况吗?

Furthermore, the above code retains the primary key value (ID) when moved into table B. Is there a way to prevent this?

推荐答案

如果表A和B具有用于记录过滤的相同列，则可以仅使用WHERE子句来搜索表格.但是在某些情况下可能会很困难，特别是如果用户能够手动过滤子窗体.在这种情况下，您可以这样创建WHERE子句:

If your tables A and B have the same columns for records filtering, you can just use WHERE clause used for searching rows in A table. But in some cases it may be difficult, especially if user is able to filter the subform manually. In this case you can create WHERE clause like this:

WHERE [ID] in (1,3,5,7....)

如果子表单中有很多行，则此SQL可能超出了SQL字符串的长度限制，因此将移动分为几块是合理的:

If there are a lot of rows in the subform, this SQL may exceed SQL string length limit, so it's reasonable to split the moving into few chunks:

Dim lngCounter As Long
Dim rst As DAO.Recordset
Dim strWhere As String
Dim strSQL As String

Const lngChunkSize = 100

Set rst = Me.RecordsetClone
If rst.RecordCount > 0 Then
    rst.MoveFirst

    Do Until rst.EOF
        lngCounter = lngCounter + 1
        'Collect keys
        strWhere = strWhere & rst!ID & ","
        rst.MoveNext

        If lngCounter Mod lngChunkSize = 0 Or rst.EOF Then
            'chunk is full or end operations, move rows
            strWhere = Left(strWhere, Len(strWhere) - 1) 'remove last coma
            strSQL = "INSERT INTO B ([a], [b], [c]) " & _
                     "SELECT [a], [b], [c] From A where ID in (" & strWhere & ")"

            'use ADO, it supports longer strings
            CurrentProject.Connection.Execute strSQL

            strSQL = "DELETE * From A where ID in (" & strWhere & ")"
            CurrentProject.Connection.Execute strSQL

            strWhere = ""
        End If
    Loop
End If

这种方法比通过记录集的一一循环快得多.速度类似于在所有条件下使用普通" WHERE子句.
在我的情况下，块大小= 100对于性能而言是最佳的，您可以尝试使用不同的块大小并为表找到最佳的大小.

This way works much faster than one-by one looping thru the recordset. The speed is similar to using "normal" WHERE clause with all conditions.
Chunk size=100 was most optimal for performance in my case, you can try different chunk sizes and find most optimal size for your table.

这篇关于将记录从一个表移动到另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！