Want to create a make table query using vba

[sam15024]

Hi I am newbie, can you please help me to create this query through vba

SELECT [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5
FROM 010710_pri
GROUP BY [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5
HAVING (((Count(*))=1));
 

I want to create a make table query.

[nico5038]

Just change the type from SELECT Into "Make Table".
The type can be set in the header menu / Ribbon.

Nic;o)

[sam15024]

Hi Nico,
Thanks for your reply, however my below mentioned command button is not working can you please take a look?

Private Sub Command5_Click()
On Error Resume Next
Dim dbs As Database
Dim strSQL As String
Dim strQueryName As String
Dim qryDef As QueryDef
Set dbs = CurrentDb()
strQueryName = "Query1"
dbs.QueryDefs.Delete strQueryName
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
DoCmd.RunSQL ("SELECT [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5" & _
"INTO 010710" & _
"FROM 010710_pri" & _
"GROUP BY [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5" & _
"HAVING (((Count(*))=1));")
End Sub
 

Regards,
Sam

[nico5038]

Just put the whole string into a query (open the SQL mode with the button top left), test it and finally save it and use in your code:

currentdb.execute ("qryInsert")
 

I do have some hesitations about using a number as tabelname, try to start every table with "tbl" as a prefix !

Nic;o)

[MMcCarthy]

Please use code tags when posting code

Check out How to ask a question