make table query

[ken]

Hi,
I use this command to run a make table query without opening it...
CurrentDb.Execu te "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.execu te?

[Danny J. Lesandrini]

Re: make table query

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
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"ken" <gevayl@gmail.c om> wrote ...[color=blue]
> Hi,
> I use this command to run a make table query without opening it...
> CurrentDb.Execu te "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.execu te?
>[/color]

[Ed Robichaud]

Re: make table query

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.SetWarnin gs False
DoCmd.OpenTable "tblZZZ"
DoCmd.RunComman d acCmdSelectAllR ecords
DoCmd.RunComman d acCmdDeleteReco rd
DoCmd.OpenQuery "qryMakeZZZ ", acViewNormal, acEdit
DoCmd.close acTable, "tblZZZ"
DoCmd.SetWarnin gs True


"ken" <gevayl@gmail.c om> wrote in message
news:1134999333 .994922.307350@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> Hi,
> I use this command to run a make table query without opening it...
> CurrentDb.Execu te "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.execu te?
>[/color]

[Danny J. Lesandrini]

Re: make table query

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
dlesandrini@hot mail.com

http://amazecreations.com/datafast

"Ed Robichaud" <edrobichaud@wd n.com> wrote ...[color=blue]
> 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.SetWarnin gs False
> DoCmd.OpenTable "tblZZZ"
> DoCmd.RunComman d acCmdSelectAllR ecords
> DoCmd.RunComman d acCmdDeleteReco rd
> DoCmd.OpenQuery "qryMakeZZZ ", acViewNormal, acEdit
> DoCmd.close acTable, "tblZZZ"
> DoCmd.SetWarnin gs True
>
>
> "ken" <gevayl@gmail.c om> wrote in message news:1134999333 .994922.307350@ z14g2000cwz.goo glegroups.com.. .[color=green]
>> Hi,
>> I use this command to run a make table query without opening it...
>> CurrentDb.Execu te "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.execu te?
>>[/color]
>
>[/color]

[ken]

Re: make table query

Hi...yea I think..delete then execute is the way to go.

Thank you all very much...