Make Table Query and Delete Query Access VBA

Question

I'm working from Excel and automatically opening up Access and running a macro that will import data, refresh my queries, and export them.

However, two of my queries are more difficult. I have a make table query that runs from a Find Duplicates query and then a delete query that takes out the duplicates from the same table. I am assuming that if I want the table to update I have to re-run the make table and delete queries with my newly imported data. How can I do this in Access?

I have tried looking it up and it appears that maybe the runSQL command is what I should use? I'm not sure how to format it though, especially because when I try to include my SQL version of my queries, VBA doesn't accept it.

Also, should I do this as a separate macro? Or can I run it within the one that I am working in?

Here is the SQL from the two queries:

For the make table query:

SELECT [NameDupQuery1].* INTO [TableMakingAndEditing]
FROM [NameDupQuery1];

For the delete query:

DELETE [TableMakingAndEditing].Carrier, [TableMakingAndEditing].[Field1], [TableMakingAndEditing].[Field2], [TableMakingAndEditing].[Field3], [TableMakingAndEditing].[Field4], [TableMakingAndEditing].[Field5], [TableMakingAndEditing].[Field6], [TableMakingAndEditing].[Field7], [TableMakingAndEditing].[Field8]
FROM [TableMakingAndEditing]
WHERE ((([TableMakingAndEditing].Carrier) In (SELECT [Carrier] FROM [TableMakingAndEditing] As Tmp GROUP BY [Carrier],[Field1],[Field2] HAVING Count(*)>1  And [Field1] = [TableMakingAndEditing].[Field1] And [Field2] = [TableMakingAndEditing].[Field2])));

Any help or suggestions?

Lui Lui · Accepted Answer · 2016-11-07T19:01:28

Never mind! I found out that as long as I already have the queries made, just using

DoCmd.SetWarnings False
DoCmd.OpenQuery ("MakeTable Query")
DoCmd.OpenQuery ("Delete from table query")
DoCmd.SetWarnings True

Will run my make table query and the delete query! Awesome!

Make Table Query and Delete Query Access VBA

1 Answers