By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,827 Members | 2,220 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,827 IT Pros & Developers. It's quick & easy.

Want to create a make table query using vba

P: 2
Hi I am newbie, can you please help me to create this query through vba
Expand|Select|Wrap|Line Numbers
  1. SELECT [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5
  2. FROM 010710_pri
  3. GROUP BY [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5
  4. HAVING (((Count(*))=1));
I want to create a make table query.
Jan 12 '10 #1
Share this Question
Share on Google+
4 Replies

Expert 2.5K+
P: 3,072
Just change the type from SELECT Into "Make Table".
The type can be set in the header menu / Ribbon.

Jan 12 '10 #2

P: 2
Hi Nico,
Thanks for your reply, however my below mentioned command button is not working can you please take a look?
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command5_Click()
  2. On Error Resume Next
  3. Dim dbs As Database
  4. Dim strSQL As String
  5. Dim strQueryName As String
  6. Dim qryDef As QueryDef
  7. Set dbs = CurrentDb()
  8. strQueryName = "Query1"
  9. dbs.QueryDefs.Delete strQueryName
  10. Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
  11. DoCmd.RunSQL ("SELECT [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5" & _
  12. "INTO 010710" & _
  13. "FROM 010710_pri" & _
  14. "GROUP BY [010710_pri].Field1, [010710_pri].Field2, [010710_pri].Field3, [010710_pri].Field4, [010710_pri].Field5" & _
  15. "HAVING (((Count(*))=1));")
  16. End Sub
Jan 12 '10 #3

Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. currentdb.execute ("qryInsert")
I do have some hesitations about using a number as tabelname, try to start every table with "tbl" as a prefix !

Jan 12 '10 #4

Expert Mod 10K+
P: 14,534
Please use code tags when posting code

Check out How to ask a question
Jan 12 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.