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 · Accepted Answer · 2016-11-07 19:01:28Z

1

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!

answered Nov 7, 2016 at 19:01

Lui

533 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Make Table Query and Delete Query Access VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related