Variable table name

Hi,

I've looked around on the forum to see if this has been answered before and couldn't find anything specific, but please be patient if it has been answered.

I'm reworking a set of queries that are used at my workplace to create periodic snapshots. The last step is a make-table query, and I'd like to be able to name the resulting table without changing the acutal SQL code, i.e.

SELECT ... INTO table_name_goes_here

I'm using Access (not my choice) and ideally would have the user enter the table name in a form.

Any ideas or suggestions?

Thanks

What is wrong with Access. It can be very powerful in the right persons hands.


What you want to do is write VBA code that executes you SQL statement. You will pass your table name to the sql as a variable

Dim dbs As Database
Dim TableName as string
DIm SQL as string
Set dbs = CurrentDb()
TableName = "NewTable"
SQL = "Select * INTO " & TableName & " From OldTableName"
dbs.Execute SQL
dbs.Close


S-

Thanks, that pointed me in the right direction.
I'm not trying to malign access, just so suggestions would only deal with Access.

Thanks for your help.

I seem to have a problem that bugs me quite a lot and it is subject related.
I am requesting a Name for the table I need to create and it reports the error message when I use the variable in place of the TableName:

Syntax error in CREATE TABLE statement.

Here is the code:
CTBLName = Request.Form("TBOXName")

strSQLCreate = "CREATE TABLE" & CTBLName & "(ID AUTOINCREMENT,Column1 varchar,Column1 varchar,Column1 varchar,Column1 varchar)"

When The statement is without a variable:

strSQLCreate = "CREATE TABLE TableName (ID AUTOINCREMENT,Column1 varchar,Column1 varchar,Column1 varchar,Column1 varchar)"

There is no error message. I've been using variables before in this manner but not in place of a table name.

Can you please help me a bit?

Thank you.