rename a table

[Alvin]

How do I rename a table from a form input. I can't get the logistics correct.
I have a table that contains my inventory. When I get an order, I enter the quantity need through a form. I the have a query that creates a new table with just the ordered items. I want to be able to use a form entry to name the new table with a unique order number.
I'm new at this and can't get the mechanics down.
Thanks for any help.

 

[Jack Cowley]

Hmmm. I would suggest that you look into normalization and maybe a sample inventory database or two. You do not want to be adding/renaming tables for any reason. Also, inventory control in a database is something of a science and I would suggest that you read as much as you can on this subject. This article might be a good place to start...

hth,
Jack

 

[Alvin]

Can I create a way to specify the name of the new table that is created by the make table query?

 

[Rich]

I am curious, why do you ask the advice of an expert and then ignore it?

 

[Alvin]

I ask the advice of an expert because I don't know the answer. I think my question was misinterpreted. I am not trying to create inventory control. I would like to know if, when I create a "make table" query, can I have the option of creating a name for the new table each time I run the query, without returningto query design. The end result is that I would have a separate table for each order.

I did research the response, but it didn't apply to my problem. The prompt response is appreciated though.

Thanks

 

[Jack Cowley]

Here is one way that you can do what you want. I will assume an unbound control on a form where the user will enter the name of the table they want to create:

Dim strSQL As String
Dim MyVariable As String
MyVariable = Me.ControlOnForm

strSQL = "SELECT MyTable.CustID, MyTable.LastName, MyTable.FirstName, MyTable.Satisfaction " _
& "INTO " & MyVariable & " FROM MyTable;"

DoCmd.RunSQL strSQL

You can do this with either DAO code or ADO as well....

hth,
Jack

 

[Rich]

I may be misunderstanding you but are you saying that if you have say 50 orders you will create 50 new tables?