MAKE A NEW TABLE SQL

[rebelscum0000]

Dear All,

I want to Make a New Table (Action querie), with "Structure Only",
(Like when I select a
Table. Left click on it, Select "Copy" and later Left Click "Paste" in
my Current DB)) In
Visual Basic.

I have a Form Called MainExclude_Form, with a Control Source called
MyAppz
I want to use MyAppz to name my New Table

My Record Source of the Form comes from a Table Called MainExclude_Tbl
with the Followings FieldName :

ID (Key, Autonumber)
MyKeword
MyAppz
FileName
FolderPath
FileExtension
FileType
FileSize
FileModified
FileAccessed
FileCreated
FileAtributes
MyInclude

I created a New Query, Selected all FieldName, of MainExclude_Tbl,
Then Rigth Click Select Query Type and click on Make-Table Query
Enter the Table Name: Symantec_Tbl (as example)
And the resul of the SQL View are:

SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword,
MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName,
MainExclude_Tbl.FolderPath, MainExclude_Tbl.FileExtension,
MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize,
MainExclude_Tbl.FileModified, MainExclude_Tbl.FileAccessed,
MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes,
MainExclude_Tbl.MyInclude INTO Symantec_Tbl
FROM MainExclude_Tbl;

When I ran it works perferct, it created the new Table but with all
data From
Table MainExclude_Tbl

I do not want that!

First I do not have any idea How to build my SQL statement to Execute
into my code
TO MAKE A NEW Table; BuT I want something like:

sQL4 = _
INSERT ALL THE FIELDS
FROM MainExclude_Tbl
INTO a NEW TABLE (MYAppz (TABLE))
WHERE ALL The recordS OF The NEW TABLE are blank

CurrentDb.Execute sQL4, dbFailOnError

As you can Read I am lost

PLease help me

Thank you very much in advance

Regards,
Antonio Macias

 

[Douglas J. Steele]

Change your SQL to:

SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword,
MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName,
MainExclude_Tbl.FolderPath, MainExclude_Tbl.FileExtension,
MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize,
MainExclude_Tbl.FileModified, MainExclude_Tbl.FileAccessed,
MainExclude_Tbl.FileCreated, MainExclude_Tbl.FileAttributes,
MainExclude_Tbl.MyInclude INTO Symantec_Tbl
FROM MainExclude_Tbl WHERE False;

(In other words, add "WHERE False" to the end of it)

 

[John Vinson]

I want to Make a New Table (Action querie), with "Structure Only",

Douglas's answer is of course correct - I'd just like to warn you that
this should NOT be a routine part of your application. MakeTable
queries can be useful when you're initially setting up a database, but
I can concieve of VERY few cases where users would be allowed to run
them; those cases would involve temporary tables in a separate
database created on the spot for that purpose, and should be run
*only* if it's impossible or impractical for a select query to do the
job.

If you're seeing this as something that will be done repeatedly and
routinely, you may want to reconsider your design!

John W. Vinson[MVP]

 

[rebelscum0000]

Thank you! Douglas this code works perfect (I visited your webpage new
sources To add WebPages as favorites!)

Also, I will keep in mind the warning, The Master John Talked about,
Until now, nothing is broken, If I have to re-design, I will do it, I
will not give up

However, I still have a problem here, and I always stop with the same
situation:
Quotes, Ampersands, Parenthesis....

I am writing this because when I have to use variables in this case
Building a SQL I do not how to deal with these characters
Is there a web page where I can learn more or a rule to Memorize?

I am going to focus in the "INTO"

As I said in my form, I have a Field called MyAppz and changes when I
enter a new record

If I left

MainExclude_Tbl.MyInclude INTO MyAppz

*This will be the name of my New Table

I want to use MyAppz to name the New Table something like :

"MainExclude_Tbl.MyInclude INTO '" & Appz & "'" & _

I have tried adding or deleting Quotes, Ampersands, Parenthesis and
nothing works

I always get a Run-Time error as

Run-Time error '3450'
Syntax error in query. Incomplete query clause

My new code:

SQL6 = "SELECT MainExclude_Tbl.ID, MainExclude_Tbl.MyKeyword, " & _
"MainExclude_Tbl.MyAppz, MainExclude_Tbl.FileName, " & _
"MainExclude_Tbl.FolderPath,
MainExclude_Tbl.FileExtension, " & _
"MainExclude_Tbl.FileType, MainExclude_Tbl.FileSize, " & _
"MainExclude_Tbl.FileModified,
MainExclude_Tbl.FileAccessed, " & _
"MainExclude_Tbl.FileCreated,
MainExclude_Tbl.FileAttributes, " & _
"MainExclude_Tbl.MyInclude INTO '" & Appz & "'" & _
"FROM MainExclude_Tbl " & _
"WHERE False;"

CurrentDb.Execute SQL6, dbFailOnError

Is this possible? Any Suggestions??

Thanks in advance

Kind Regards
Antonio Macias

 

[Douglas J. Steele]

Two things that I see.

1) You don't use single quotes around table names. If the table name has
spaces in it, you need to use [ and ] around it, but not quotes.

2) You're missing a space in front of the FROM keyword.

Both errors will be corrected by changing

"MainExclude_Tbl.MyInclude INTO '" & Appz & "'" & _

to

"MainExclude_Tbl.MyInclude INTO [" & Appz & "] " & _