Rename a table

[merry_fay]

Hi,

I want to rename or create a table so it has the name from this entry cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make table query
to do this?

Thanks
merry_fay

 

[kc-mass]

Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM " &
Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new name.


Regards

Kevin

 

[kc-mass]

Instead use this. Dangerous to not see errors before dropping a table.


Sub NewTable()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!frmTableRename!txtNewTable & " FROM "
& Forms!frmTableRename!txtOldTable & " ;"
CurrentDb.Execute (strSQL), dbFailOnError
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
CurrentDb.Execute (strSQL), dbFailOnError
End Sub

Kevin

Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM " &
Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new name.


Regards

Kevin

Hi,

I want to rename or create a table so it has the name from this entry
cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make table
query
to do this?

Thanks
merry_fay

 

[Bob Barrows]

Euwww - what about all the carefully crafted indexes, Formats, etc. ..?

To merry_fay: which do you want to do? Rename an existing table or
create a new table? These are two very different operations.

Renaming a table is very dangerous. It's not just a matter of renaming
the table: you also have to worry about cascading that name change to
all the queries, subs, functions and macros that refer to it. I have
heard that Rick Fisher's Find And Replace is a great tool to help with
this process.

Object names cannot be dynamic in saved queries, so the fast answer to
your question is "No". VBA code will have to be used. My preference
would be to use a DAO tabledef object for either purpose.

Renaming:
Dim db as database,tdf as tabledef
set db=currentdb
set tdf=db.tabledefs(Forms!frmDashBoard!txtOldTable)
tdf.name = Forms!DashBoard!txtNewTable
' you will need to write code to deal with other objects
' that refer to this tabledef

Creating:
Here is an example from online help:
Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.

..Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub

Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM
" & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new name.


Regards

Kevin

Hi,

I want to rename or create a table so it has the name from this entry
cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make
table query
to do this?

Thanks
merry_fay

 

[kc-mass]

Hi Bob

If a table is part of a real DB Design/Schema proper there is no reason,
logic or utility to renaming it or any other object. Why would you do that
no matter the method of its execution?

The typical place I've seen renaming done is in the import process used by
by users who want to set data aside (not developers) - about 85% of Access
use.
..
They want to automate their monthly reporting cycles to make their work
easier and results speedier. They want to get the info up the chain. They
are not into nor do they care about the X normal form. IT gives no support
and training courses have dissappered. They typically use few if any
indexes and relations are set only in queries and there only if it cannot be
avoided.

Sometimes people seem to ask to do things that are undesireable, but they
are sitting at their desk trying to get through their work and they have
calculated their approach. Sometimes you should just answer their question.

Kevin

Euwww - what about all the carefully crafted indexes, Formats, etc. ..?

To merry_fay: which do you want to do? Rename an existing table or
create a new table? These are two very different operations.

Renaming a table is very dangerous. It's not just a matter of renaming
the table: you also have to worry about cascading that name change to
all the queries, subs, functions and macros that refer to it. I have
heard that Rick Fisher's Find And Replace is a great tool to help with
this process.

Object names cannot be dynamic in saved queries, so the fast answer to
your question is "No". VBA code will have to be used. My preference
would be to use a DAO tabledef object for either purpose.

Renaming:
Dim db as database,tdf as tabledef
set db=currentdb
set tdf=db.tabledefs(Forms!frmDashBoard!txtOldTable)
tdf.name = Forms!DashBoard!txtNewTable
' you will need to write code to deal with other objects
' that refer to this tabledef

Creating:
Here is an example from online help:
Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.

.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub

Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM
" & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new name.


Regards

Kevin

Hi,

I want to rename or create a table so it has the name from this entry
cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make
table query
to do this?

Thanks
merry_fay

 

[Bob Barrows]

Umm ... I did answer the question ... no need for you to get defensive.

Hi Bob

If a table is part of a real DB Design/Schema proper there is no
reason, logic or utility to renaming it or any other object. Why
would you do that no matter the method of its execution?

The typical place I've seen renaming done is in the import process
used by by users who want to set data aside (not developers) - about
85% of Access use.
.
They want to automate their monthly reporting cycles to make their
work easier and results speedier. They want to get the info up the
chain. They are not into nor do they care about the X normal form.
IT gives no support and training courses have dissappered. They
typically use few if any indexes and relations are set only in
queries and there only if it cannot be avoided.

Sometimes people seem to ask to do things that are undesireable, but
they are sitting at their desk trying to get through their work and
they have calculated their approach. Sometimes you should just
answer their question.

Kevin

Euwww - what about all the carefully crafted indexes, Formats, etc.
..?

To merry_fay: which do you want to do? Rename an existing table or
create a new table? These are two very different operations.

Renaming a table is very dangerous. It's not just a matter of
renaming the table: you also have to worry about cascading that name
change to all the queries, subs, functions and macros that refer to
it. I have heard that Rick Fisher's Find And Replace is a great tool
to help with this process.

Object names cannot be dynamic in saved queries, so the fast answer
to your question is "No". VBA code will have to be used. My
preference would be to use a DAO tabledef object for either purpose.

Renaming:
Dim db as database,tdf as tabledef
set db=currentdb
set tdf=db.tabledefs(Forms!frmDashBoard!txtOldTable)
tdf.name = Forms!DashBoard!txtNewTable
' you will need to write code to deal with other objects
' that refer to this tabledef

Creating:
Here is an example from online help:
Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.

.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub

Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM
" & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new
name.


Regards

Kevin


Hi,

I want to rename or create a table so it has the name from this
entry cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make
table query
to do this?

Thanks
merry_fay

 

[kc-mass]

Hi Bob,

Not defensive, instructive perhaps but not defensive.

Regards

Kevin

Umm ... I did answer the question ... no need for you to get defensive.

Hi Bob

If a table is part of a real DB Design/Schema proper there is no
reason, logic or utility to renaming it or any other object. Why
would you do that no matter the method of its execution?

The typical place I've seen renaming done is in the import process
used by by users who want to set data aside (not developers) - about
85% of Access use.
.
They want to automate their monthly reporting cycles to make their
work easier and results speedier. They want to get the info up the
chain. They are not into nor do they care about the X normal form.
IT gives no support and training courses have dissappered. They
typically use few if any indexes and relations are set only in
queries and there only if it cannot be avoided.

Sometimes people seem to ask to do things that are undesireable, but
they are sitting at their desk trying to get through their work and
they have calculated their approach. Sometimes you should just
answer their question.

Kevin

Euwww - what about all the carefully crafted indexes, Formats, etc.
..?

To merry_fay: which do you want to do? Rename an existing table or
create a new table? These are two very different operations.

Renaming a table is very dangerous. It's not just a matter of
renaming the table: you also have to worry about cascading that name
change to all the queries, subs, functions and macros that refer to
it. I have heard that Rick Fisher's Find And Replace is a great tool
to help with this process.

Object names cannot be dynamic in saved queries, so the fast answer
to your question is "No". VBA code will have to be used. My
preference would be to use a DAO tabledef object for either purpose.

Renaming:
Dim db as database,tdf as tabledef
set db=currentdb
set tdf=db.tabledefs(Forms!frmDashBoard!txtOldTable)
tdf.name = Forms!DashBoard!txtNewTable
' you will need to write code to deal with other objects
' that refer to this tabledef

Creating:
Here is an example from online help:
Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.

.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _

prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub


kc-mass wrote:
Try Something Like :

Sub NewTableName()
Dim strSQL As String
strSQL = "SELECT * INTO " & Forms!DashBoard!txtNewTable & " FROM
" & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
strSQL = "DROP TABLE " & Forms!frmDashBoard!txtOldTable & " ;"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End Sub

This uses two text boxes, one the original name and two the new
name.


Regards

Kevin


Hi,

I want to rename or create a table so it has the name from this
entry cell:
Forms![Dashboard]![TableName]

Is there a way of using the Rename macro or in the SQL of a make
table query
to do this?

Thanks
merry_fay