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

make table query

P: n/a
ken
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.

Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...

Anything else I could use other then Currentdb.execute?

Dec 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ken:

What, exactly, do you want to accomplish? A MakeTable query
makes a table, thus the name. If the table already exists, the
Execute statement will give you an error that the table exists.

So, if you want to replace the table, try running a drop table
command first
DoCmd.Execute "Drop Table tblYourTable"

Be warned, that this will delete your table and all its data.

--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"ken" <ge****@gmail.com> wrote ...
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.

Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...

Anything else I could use other then Currentdb.execute?

Dec 19 '05 #2

P: n/a
The code below deletes records from an existing table, then runs an append
query to add records to that table; all without user input or interference.
-Ed

DoCmd.SetWarnings False
DoCmd.OpenTable "tblZZZ"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.OpenQuery "qryMakeZZZ", acViewNormal, acEdit
DoCmd.close acTable, "tblZZZ"
DoCmd.SetWarnings True
"ken" <ge****@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.

Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...

Anything else I could use other then Currentdb.execute?

Dec 19 '05 #3

P: n/a
OUCH!

Dude, that's not the way to do this. Again, what do you want to do?
Don't try to manipulate the table using Access menus. Write the sql.

If you want to make the table then make the table. If you want to
delete all records and append new ones, then do that.

Dim dbs As DAO.Database
Dim sSQL As String

Set dbs = CurrentDB()

' 1) Delete and append records
sSQL = "DELETE FROM tblZZZ"
dbs.Execute sSQL
sSQL = "INSERT INTO tblZZZ SELECT * FROM qryZZZ"
dbs.Execute sSQL

' 2) Drop and recreate table
sSQL = "DROP TABLE tblZZZ"
dbs.Execute sSQL
sSQL = "SELECT * INTO tblZZZ FROM qryZZZ"
dbs.Execute sSQL

No menus, no RunCommand ... just pure SQL. It works, it's clean
it's readable, it's efficient. But first, you need to be clear about
what you wish to accomplish. I'm still not clear on that.
--

Danny J. Lesandrini
dl*********@hotmail.com
http://amazecreations.com/datafast
"Ed Robichaud" <ed*********@wdn.com> wrote ...
The code below deletes records from an existing table, then runs an append query to add records to that table; all
without user input or interference.
-Ed

DoCmd.SetWarnings False
DoCmd.OpenTable "tblZZZ"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.OpenQuery "qryMakeZZZ", acViewNormal, acEdit
DoCmd.close acTable, "tblZZZ"
DoCmd.SetWarnings True
"ken" <ge****@gmail.com> wrote in message news:11**********************@z14g2000cwz.googlegr oups.com...
Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execute "make table query name"
Access tells me that it can't execute a select query...? Its a make
table query not a select? Or are those two the same.

Bottom line is that the DoCmd.OpenQuery alowes the user to see the
query and I don't want that...

Anything else I could use other then Currentdb.execute?


Dec 19 '05 #4

P: n/a
ken
Hi...yea I think..delete then execute is the way to go.

Thank you all very much...

Dec 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.