Make Table Naming Convention

D

Dawn

When creating a make table query, is it possible to add a parameter value to
the naming syntax in the make table name?

Thanks
 
D

Duane Hookom

You can't use a parameter prompt to set or modify the "made" table name. You
could use code to change the SQL property of the saved query.
 
F

fredg

When creating a make table query, is it possible to add a parameter value to
the naming syntax in the make table name?

Thanks
Click to expand...

Do you mean have the query prompt for the name of the table to make
after the query has already been created?
No, I don't think so.

However, no reason why you can't do it using RunSQL

Dim strTableName as String
strTablename = InputBox("Name of the new table")
DoCmd.RunSQL "Select YourTable.* Into " & strTableName & " From
YourTable;"

or CurrentDb.Execute,

Dim strTableName as String
strTablename = InputBox("Name of the new table")
CurrentDb.Execute "Select YourTable.* Into " & strTableName & " From
YourTable;",dbFailOnError

or even just run the query using the existing table name and then
renaming the table in code:

DoCmdOpenQuery "YourMakeTableQuery"
Dim strTablename as String
strTableName = InputBox("Name of the new Table")
DoCmd.Rename strNewTable , acTable, "OriginalTablename"
 
D

Duane Hookom

Good code fred however some users still use spaces in object names :-( You
may need to revise your code to add []s.
Dim strTableName as String
strTablename = InputBox("Name of the new table")
DoCmd.RunSQL "Select YourTable.* Into [" & strTableName & "] From YourTable;"
 
J

John W. Vinson

When creating a make table query, is it possible to add a parameter value to
the naming syntax in the make table name?

Thanks
Click to expand...

I see you've gotten good answers but... I'd like to "unask" the question.

In my opinion, MakeTable queries are very rarely necessary at all. Prompting
the user for table names will just add a proliferation of badly-named tables
to your database; and having multiple tables with the same structure is pretty
much always a bad idea in any case!

If you're assuming that you need a table in order to edit or report subsets of
data, your assumption needs revising. You can base a Report or a Form on a
query; you can export from a query; you can do essentially *anything* with a
Query that you can do with a Table. Consider whether you even need these made
tables!

John W. Vinson [MVP]
 
Top