We help IT Professionals succeed at work.

How to create a parameter value for the "Into" portion of a make table query in Access 2010

frankmedina
frankmedina used Ask the Experts™
on
In Microsoft Access 2010, I am trying to run the following query:

SELECT tblProcessTime.AutoNum, tblProcessTime.PartType, tblProcessTime.DistributionType, tblProcessTime.Para1, tblProcessTime.Para2, tblProcessTime.Para3 INTO [NewTableName]
FROM tblProcessTime;

Where I am assuming that [NewTableName] is a parameter value where Access will prompt the user for a "New Table Name" for the new table that is being created from the "Make Table" query

However when I run the query, the query just creates a new table called "NewTableName".

This is not my intent. My intent is that the user will have the ability to name the new table that is being created by the Make Table query.

Can a Make Table query  have parameter values? If so, can it have a parameter value for the "INTO" command portion? If so, please advise how to modify my query.

Attached is the Access database document which has the query named "MakeNewTable" which refers to my above inquiry. If you need anything further, please feel free to ask. Thanks.
Stage-2.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Access can't use parameter in query for table name, but there is other way:
1. Create form with field for new table name  (NewTableName)
2. Create button with code:
Dim Sql As string
If Me.NewTableName & "" <> "" then
 SQL = "SELECT tblProcessTime.AutoNum, tblProcessTime.[PartType] AS var1, "
 SQL = SQL & "tblProcessTime.DistributionType, tblProcessTime.Para1, tblProcessTime.Para2, "
 SQL = SQL & "tblProcessTime.Para3 INTO " & Me.NewTableName & " FROM tblProcessTime;"
 Currentdb.execute SQL, , dbFailOnError
end if

Open in new window

And as a proposal - it is not very good idea to give user ability to create tables in DB. If you think it is absolutely necessary, check existance of table before overwriting it.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That can't be done in SQL. Parameters are for fields only.

/gustav

Author

Commented:
Hi als315,

Thanks for the information. I do not think I can use the form technique you recommended in my particular case.

To give you some background, I am working on a project for my decision support systems class and the attached Access databse ties into Microsoft Visual Studio 2010 and ASP.NET.
Using VB.NET code, I will need to programmatically execute the Make Table query above and somehow get the "New Table Name" from the user from a textbox or the like.

Once I get the "New Table Name" I need to pass it up as a parameter value so that the new table created (from the Make Table query) has the specific "New Table Name" inputted from the user.

Below is sample code of a programmatically executed query with parameter values:

Dim myDB As New OleDbConnection(myConnection)
       myDB.Open()

     Dim sqlcmd As New OleDbCommand(" ", myDB)

       sqlcmd.CommandText = "INSERT INTO tblProcess ( ProcessName, Mean, StdDev, ResourceNumber ) SELECT [Name?] AS Expr1, [Mean?] AS Expr2, [std?] AS Expr3, [cap?] AS Expr4"
     sqlcmd.Parameters.AddWithValue("[Name?]", tbxProc.Text)
     sqlcmd.Parameters.AddWithValue("[Mean?]", CDbl(tbxMean.Text))
     sqlcmd.Parameters.AddWithValue("[std?]", CDbl(tbxSD.Text))
      sqlcmd.Parameters.AddWithValue("[cap?]", CInt(tbxCap.Text))
      sqlcmd.ExecuteNonQuery()

With this code, Visual Studio executes the query in the sqlcmd.commandtext in Access and then passes up the parameters with the "sqlcmd.parameters.addwithvalue" command.

Then it executes the query with the "sqlcmd.ExecuteNonQuery()"

Hope this makes sense. Let me know if you can think of something else. Thanks.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You will need something like:

strNewTableName = "tblNewProcessTable"
sqlcmd.CommandText = "INSERT INTO " + strNewTableName + " ( ProcessName, Mean, StdDev,

/gustav

Author

Commented:
Hi Gustav,

Thanks for the information..

I thought I could not embed a variable into the "sqlcmd.CommandText" command. Isn't that why we have the "sqlcmd.Parameters.AddWithValue" commands?

So in my original case, Are you recommending to modify my code to something like the following?

strNewTableName = "tblNewProcessTable"

sqlcmd.CommandText = "SELECT tblProcessTime.AutoNum, tblProcessTime.PartType, tblProcessTime.DistributionType, tblProcessTime.Para1, tblProcessTime.Para2, tblProcessTime.Para3 INTO " + strNewTableName + "
FROM tblProcessTime";

Thanks...
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Yes, that should work. The CommandText property is nothing but a text string.

/gustav