SQL sentence for make table

#1 (**permalink**) 28-Jul-2006, 08:19 AM

hi all,
i built SQL sentences that are making tables dinamicly,
my problem is that everytime a table is going to be made,the is a
message asking for approval of the make table.
how can i make the table without this message?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11232-sql-sentence-for-make-table.html

*

#2 (**permalink**) 28-Jul-2006, 08:19 AM

instead of using

doCmd.RunSQL strSQL
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232

use

CurrentDb.Execute strSQL, dbFailOnError

why are you making a bunch of tables as opposed to using
select queries?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

yaniv d wrote:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> hi all,
> i built SQL sentences that are making tables dinamicly,
> my problem is that everytime a table is going to be made,the is a
> message asking for approval of the make table.
> how can i make the table without this message?
>

#3 (**permalink**) 28-Jul-2006, 08:19 AM

Yaniv,

I assume you are using the
DoCmd.OpenQuery
method in your code, to run the make-table query.

You can put this before...
DoCmd.SetWarnings False

.... and this after...
DoCmd.SetWarnings True

--
Steve Schapel, Microsoft Access MVP

yaniv d wrote:
> hi all,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> i built SQL sentences that are making tables dinamicly,
> my problem is that everytime a table is going to be made,the is a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> message asking for approval of the make table.
> how can i make the table without this message?
>

#4 (**permalink**) 28-Jul-2006, 08:19 AM

i need to make a table contain data from a table and a query when
opening a form and delete the table after leaving the form

thanks for your help

strive4peace wrote:
> instead of using
>
> doCmd.RunSQL strSQL
>
> use
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
>
> CurrentDb.Execute strSQL, dbFailOnError
>
> why are you making a bunch of tables as opposed to using
> select queries?
>
> Warm Regards,
> Crystal
> Microsoft Access MVP 2006
>
> *
> Have an awesome day

>
> remote programming and training
> strive4peace2006 at yahoo.com
>
> *
>
> yaniv d wrote:
> > hi all,
> > i built SQL sentences that are making tables dinamicly,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> > my problem is that everytime a table is going to be made,the is a
> > message asking for approval of the make table.
> > how can i make the table without this message?
> >

#5 (**permalink**) 28-Jul-2006, 08:19 AM

In article <1150097288.394383.10020@y43g2000cwc.googlegroups.c om>,
yaniv.dg@gmail.com says...
> hi all,
> i built SQL sentences that are making tables dinamicly,
> my problem is that everytime a table is going to be made,the is a
> message asking for approval of the make table.
> how can i make the table without this message?
>
>
You should look for a Microsoft Access 2000 Technical Article,
Fundamental Microsoft Jet SQL for Access 2000.

An example of a Make Table query:

Dim cmd As New ADODB.Command
Dim strSQL As String

cmd.ActiveConnection = CurrentProject.AccessConnection

strSQL = "CREATE TABLE MediaOptions " & _
"(media_type INTEGER NOT NULL PRIMARY KEY, " & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
"description CHAR(10) NOT NULL, " & _
"price DECIMAL(12, 4) NOT NULL, " & _
"CHECK (price >= 0.00)); "
cmd.CommandText = strSQL
cmd.Execute
'Debug.Print "Table MediaOptions created."

strSQL = "CREATE TABLE Sales " & _
"(sales_ticket INTEGER NOT NULL, " & _
"CONSTRAINT validate_sales_ticket " & _
"CHECK (sales_ticket LIKE '[0-9][0-9][0-9][0-9][0-9]'), " & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
"media_type INTEGER NOT NULL " & _
"References MediaOptions(media_type) " & _
" ON UPDATE CASCADE, " & _
"sale_date DATETIME DEFAULT Now() NOT NULL);"
cmd.CommandText = strSQL
cmd.Execute
'Debug.Print "Table Sales created."

#6 (**permalink**) 28-Jul-2006, 08:19 AM

thanks alot to all of you!!!

Michael Gramelspacher wrote:
> In article <1150097288.394383.10020@y43g2000cwc.googlegroups.c om>,
> yaniv.dg@gmail.com says...
> > hi all,
> > i built SQL sentences that are making tables dinamicly,
> > my problem is that everytime a table is going to be made,the is a
> > message asking for approval of the make table.
> > how can i make the table without this message?
> >
> >
> You should look for a Microsoft Access 2000 Technical Article,
> Fundamental Microsoft Jet SQL for Access 2000.
>
> An example of a Make Table query:
>
> Dim cmd As New ADODB.Command
> Dim strSQL As String
>
> cmd.ActiveConnection = CurrentProject.AccessConnection
>
> strSQL = "CREATE TABLE MediaOptions " & _
> "(media_type INTEGER NOT NULL PRIMARY KEY, " & _
> "description CHAR(10) NOT NULL, " & _
> "price DECIMAL(12, 4) NOT NULL, " & _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> "CHECK (price >= 0.00)); "
> cmd.CommandText = strSQL
> cmd.Execute
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> 'Debug.Print "Table MediaOptions created."
>
> strSQL = "CREATE TABLE Sales " & _
> "(sales_ticket INTEGER NOT NULL, " & _
> "CONSTRAINT validate_sales_ticket " & _
> "CHECK (sales_ticket LIKE '[0-9][0-9][0-9][0-9][0-9]'), " & _
> "media_type INTEGER NOT NULL " & _
> "References MediaOptions(media_type) " & _
> " ON UPDATE CASCADE, " & _
> "sale_date DATETIME DEFAULT Now() NOT NULL);"
> cmd.CommandText = strSQL
> cmd.Execute
> 'Debug.Print "Table Sales created."

#7 (**permalink**) 28-Jul-2006, 08:19 AM

Yaniv,

As Crystal intimated, this is a very unusual requirement, and I also
would suspect there is an easier way.

--
Steve Schapel, Microsoft Access MVP

yaniv d wrote:
> i need to make a table contain data from a table and a query when
> opening a form and delete the table after leaving the form
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232

#8 (**permalink**) 28-Jul-2006, 08:19 AM

there is a simple way.but my problem for now is that i need to make it
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
for a multi-user and that complicating the issue becaouse i need to
make tables that will be attached for every user that will use it

Steve Schapel wrote:
> Yaniv,
>
> As Crystal intimated, this is a very unusual requirement, and I also
> would suspect there is an easier way.
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
> --
> Steve Schapel, Microsoft Access MVP
>
> yaniv d wrote:
> > i need to make a table contain data from a table and a query when
> > opening a form and delete the table after leaving the form

#9 (**permalink**) 28-Jul-2006, 08:19 AM

On 12 Jun 2006 22:48:36 -0700, "yaniv d" wrote:

>there is a simple way.but my problem for now is that i need to make it
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
>for a multi-user and that complicating the issue becaouse i need to
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11232
>make tables that will be attached for every user that will use it

Why?

What can you do with individual tables which cannot be done with a
select query, filtering on the UserID field (which you might need to
add to your table)? Storing data (a user's identity) in a tablename is
probably a Very Bad Idea.

Or, could you use a split database, with the source table in the
backend, and the individual tables in each user's frontend .mdb file?

John W. Vinson[MVP]