Run Append Query Through VBA

[JMichael]

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

[ranman256]

Ive never had a problem running an append query thru code:

docmd.openquery "qaApdQry"

[JMichael]

Its the DoCmd Transferspreadsheet which wont export

[ranman256]

you cant export append queries, only select.
run append ,
then export the select qry.