Automate Initial and Incremental Queries

[JMarcus]

I have a question for you. It is something I havent done before. I have to create an interface for users to be able to run queries which I can do. The trick is the queries will have to populate into a table. The queries will then be run weekly finding new only the new entries since it was last run, likewise compared to the ones already run and added to the table. Is there an easy way to automate this for my users?

 

[arnelgp]

use update query with right join:

update target targetTable as T1 RIGHT JOIN sourceTable as T2 ON T1.commonField = T2.commonField SET T1.field1=T2.field1, T1.field2=T2=field2, .....

 

[JMarcus]

Users will press a button through a command. I would have to have the data export and update to the table at the same time. 2nd run would be the same but only new data so the 2nd would be only the data not present in the 1st run. Does this make sense.

 

[JMarcus]

I ended up trying it out but was able to get an append query to do it. How do you recomend I bypass the past the rows message for the users?

 

[JMarcus]

I need to run an initial query which will have to go to one target table, and every time the query is run thereafter it will have to only yield new results not already on the target table. I have to enable users to run it by a click of a button. Some of the data is more than 100000 rows so it has to export through Excel. I can do it through a regular query but there is a problem running the apend query through the code I've developed so looking for options to automate the process. Here is my code:

Private Sub Command94_Click()
Dim strFile As String
Dim objXL As Object
Dim objWB As Object

strFile = CurrentProject.Path & "\Test.xlsx"

If Len(Dir(strFile)) > 0 Then
Kill strFile
End If

DoCmd.TransferSpreadsheet acExport, 10, _
"Query Update Test", CurrentProject.Path & "\Test.xlsx", True


Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set objWB = objXL.Workbooks.Open(strFile)

objXL.UserControl = True
Set objXL = Nothing

strFile = CurrentProject.Path & "\Test.xlsx"
MsgBox "Data export completed", vbInformation, "Completed"
End Sub