Search code examples
vbams-access

Copy records from one Table into another by VBA

I have a DB that collects input via a Form"Abgabe_Unterlagen" into a Table "Abgabe_Unterlagen."

Tables and Form:

enter image description here

After all actions are taken and records are completed the user enters a date in the field "Datum_Weiterg"

Once a day a query is run to filter the records from the table where " Datum_Weiterg" Is NOT NULL and do an append into a new table "Ordner_Liste"

After the records are in the new table, they will be deleted from the "Abgabe_Unterlagen." table.

Query ...

enter image description here

This is awkward an a little complicated.
It would be more elegant and take less effort if this could be done by VBA code.

Would anyone be able to assist ?

Solution

  • The solution of Gustav will work.

    The same can be achieved using one append query (to transfer the recently added records to the table Ordner_Liste) and one update query (to set the transfered dates Datum_Kopiert in table Abgabe_Unterlagen). If the final target is to delete the transfered records you could as well use a delete query instead of the update query right away (new field Datum_Kopiert is not strictly required then).

    If both tables have the same fields this will work:

    INSERT INTO Ordner_Liste.* SELECT * FROM Abgabe_Unterlagen WHERE Datum_Weiterg Is Not Null

    and then

    DELETE * FROM Abgabe_Unterlagen WHERE Datum_Weiterg Is Not Null

    If large numbers of records are involved in the operation (hundreds or thousands) using action queries will be definitely faster than looping over recordsets.

    Action queries can be executed in VBA using

    CurrentDb.Execute "NameOfYourActionQuery"

    or

    DoCmd.SetWarnings false 
DoCmd.OpenQuery "NameOfYourActionQuery" 
DoCmd.SetWarnings true