Change Table name via Form?

[Guest]

How would one change a Table's name via a Form textbox?

Why? Users need to create a ProjectTable carved out from records in the
Master Tables - so the query is a MakeTable query to create a TempTable they
can work with.

User will next rename TempTable to the official ProjectNameTable - and then
go forward from there with this new table. So as they modify info for this
Project it remains recorded in this ProjectNameTable which is important.

Obviously it would be so easy just to go to Tables object and rename it
manually - - but some users freak out on this or simply do not know Access
enough to do anything other than deal with forms as presented....

So would like a Form textbox where they can enter their desired
ProjectTableName followed by a command button or maybe just AfterUpdate or
something but haven't ever done something like this involving renaming an
object from a textbox and not sure what command method is used.

The MakeTable query will of course remake the TempTable next time it is
used....and the whole process is repeated with each project.

Welcome your input...thnx

 

[Guest]

Sorry, Network, but this, overall, is not a good idea. If all these various
project tables contain the same fields, then it should not be seperated into
different tables. This creates havoc when trying to pull data that include
more than one project. It is a nightmare for keeping the row source
properties of forms in sync with the data being used. The same goes for any
queries you will need or reports that use the data.

If a user will be working with a specific project, then use a query based on
the projects table filtered by the project the user is working on.

You may think you have some valid reason(s) to do this, but in reality, any
gain you get from this will be significantly outweighed by the problems and
limitations it will impose.

If you would be kind enought to tell me the reason(s) for this approach,
perhaps I can suggest some alternatives.

 

[Guest]

Hi Klatuu,.....The easiest explanation is that the Master Table has
proprietary "list prices"...so to speak. Probably the better term is "core
price". Emphasis on the word proprietary. These records are line items with
a core price.

But when they put together a project - they gather the items they need off
the core Table....and then tweak around alot of prices - some up and some
down per management guidelines. Which are eventually presented to the
customer. If/When contracted - these project prices are then in concrete (and
also must be kept in a database as the ProjectPrices).

Each project is different. We are talking maybe a dozen to 20 projects per
year. So it isn't a burdensome quantity - and they will archive the db each
calendar year and start fresh.

There are alot of line items and the tweaking is of course an attempt to
make the sale while maximizing revenue.

In the end though - they always restart a virgin project with the core "list
prices" - and go from there. This core Master Table is big. A project table
isn't small but it is a subset.

They definitely won't accept revealing the core/list prices with a single
negotiated "discount" calculated value on the bottom line - which would be
the technique with many industries.

I agree with you that I would not normally design creating tables by project
if I had the ability to define the environment.

b.t.w. I had some discussion with them of recording just the cost variation
factor per item for a project. We were calling this a "tweak table". But
the Core Master Table does change over time - - and the TweakTable would be
linking to things in the Core Table that eventually go away or change -
rendering the historical data on that Project subject to variation which is
not good.....some real complications and data history integrity issues via
this concept too...

It is important to understand that they are already in this mode, it is
their industry norm, and they are currently doing it via an export to excel.
So why do it via excel I say?...other than a sum at the bottom it is not a
financial application. They export their query to excel - and aren't happy
in having to deal with Excel sheets that are hard to sort and filter and
such....- so I figure we Access guys can have them keep it in Access as a new
MakeTable Query instead.

My only hang up is then changing that TempTable's name via a Form's
textbox....

hope this helps....

 

[Guest]

You could put the SQL for the Make Table query in your VBA code and replace
the table name with the name you want to save it as.

 

[Guest]

good point...was in VBA thinking mode but sql could do it....will play around
with it...

 

[Guest]

In reality, it is a combination of VBA and SQL you need. Here is a way that
I use from time to time. I will use a temporary querydef. I will have one
tquery hat is a template that has all the SQL in it to perform the task I
need. I will put an indentifiable value in the SQL where I will need to
change it. Once I have the SQL modified, I will use it to create a temporary
querydef and execute that. This way, you always know what the original SQL
will look like so you can make the chages to it as needed. Something like
this:

Private Function CreateProject(strProjectName As String)
Dim strSQL as String
Dim dbf As Database
Dim qdf As QueryDef

Set dbf = CurrentDb
strSQL = dbf.QueryDefs("qtmpNewProject).SQL
strSQL = Replace(strSQL, "xxTableNamexx", strProjectName)
Set qdf = db.CreateQueryDef("", strSQL)
qdf.Execute
Set dbf = Nothing
Set dbf = Nothing
End Function