Append to a new table in diffrent db

[Dina01]

Hello,

I have the following command button but what I would like to do is that when my 2 tables get transfered to the other db, I don't want to creat a table everytime, I want to append the new records to an existing table? But I have no idea how to do this....

Here is my code and it works fine but it crate a table all the time, I only want to add the records to the other table, and not delete the ones that exist.......

Private Sub CmdTransfer_Click()

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"L:\HoraSec\Data\DispatchBackUp.mdb", acTable, "AgentTrackingModification_tb", _
"Agent_tb " & Date

DoCmd.TransferDatabase acExport, "Microsoft Access", _
"L:\HoraSec\Data\DispatchBackUp.mdb", acTable, "DataTracking_tb", _
"Data_tb " & Date

DoCmd.SetWarnings False
'Qry that deletes the AgentTrackingModification_tb
DoCmd.OpenQuery ("qryDELETEAgentForTracking")
DoCmd.SetWarnings True

DoCmd.SetWarnings False
'Qry that deletes the DataTracking_tb
DoCmd.OpenQuery ("qryDELETEDataForTracking")
DoCmd.SetWarnings True

End Sub

Also I was wondeirng if I can set a timer to my main db, so evernight when the time hits 11:59, the code runs.........

 

[Pat Hartman]

Rather than using the TransferDatabase Method, link to the remote tables and run an append query.

To automate running the export, create a separate db that has an autoexec macro that runs the transfer subroutine. You can use the scheduling feature of NT or windows, to schedule this db to be started at whatever time you specify. The new db needs links to both sets of tables. The from tables and the to tables. The rest is just the macro, a few queries, and your subroutine.

 

[Dina01]

Hey Pat,

Ok I linked the 2 tables, but I am not to sure how to do the APPEND query, can you please help.....