Renaming a table via code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

Can anyone show me how to go about rename a table in code?



SysAccountant.
 
To set a single tablename via code:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
db.TableDefs("OldName").Name = "NewName"

To put a "tbl" prefix on all table names:

Dim db As Database
Dim tdf As TableDef
Const conPrefix = "tbl"

Set db = CurrentDb()
For Each tdf in db.TableDefs
tdf.Name = conPrefix & tdf.Name
Next tdf

To reset names based on a table of old & new tablenames (named OldNewNames)

Dim db As Database
Dim tdf As TableDef
Dim varNewName As Variant

Set db = CurrentDb()
For Each tdf In db.TableDefs
varNewName = DLookup("[NewName]", "OldNewNames", "[OldName]='" &
tdf.Name & "'")
If Not IsNull(varNewName) Then
tdf.Name = varNewName
End If
Next tdf

Hope that helps.
Sprinks
 
Thanks - it was very informative

SysAccountant




Sprinks said:
To set a single tablename via code:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
db.TableDefs("OldName").Name = "NewName"

To put a "tbl" prefix on all table names:

Dim db As Database
Dim tdf As TableDef
Const conPrefix = "tbl"

Set db = CurrentDb()
For Each tdf in db.TableDefs
tdf.Name = conPrefix & tdf.Name
Next tdf

To reset names based on a table of old & new tablenames (named OldNewNames)

Dim db As Database
Dim tdf As TableDef
Dim varNewName As Variant

Set db = CurrentDb()
For Each tdf In db.TableDefs
varNewName = DLookup("[NewName]", "OldNewNames", "[OldName]='" &
tdf.Name & "'")
If Not IsNull(varNewName) Then
tdf.Name = varNewName
End If
Next tdf

Hope that helps.
Sprinks

SysAccountant said:
Hi

Can anyone show me how to go about rename a table in code?



SysAccountant.
Click to expand...
Click to expand...
 
After running Sprinks' code, you might not see the changes when you first
look in the database window. If that happens click on one of the other
objects' tabs and then back again or add this line of code to Sprinks'
Application.RefreshDatabaseWindow

--
Bill Mosca, MS Access MVP


Sprinks said:
To set a single tablename via code:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
db.TableDefs("OldName").Name = "NewName"

To put a "tbl" prefix on all table names:

Dim db As Database
Dim tdf As TableDef
Const conPrefix = "tbl"

Set db = CurrentDb()
For Each tdf in db.TableDefs
tdf.Name = conPrefix & tdf.Name
Next tdf

To reset names based on a table of old & new tablenames (named
OldNewNames)

Dim db As Database
Dim tdf As TableDef
Dim varNewName As Variant

Set db = CurrentDb()
For Each tdf In db.TableDefs
varNewName = DLookup("[NewName]", "OldNewNames", "[OldName]='" &
tdf.Name & "'")
If Not IsNull(varNewName) Then
tdf.Name = varNewName
End If
Next tdf

Hope that helps.
Sprinks

SysAccountant said:
Hi

Can anyone show me how to go about rename a table in code?



SysAccountant.
Click to expand...
Click to expand...
 
I wondered about that, Bill. Thanks.

Bill Mosca said:
After running Sprinks' code, you might not see the changes when you first
look in the database window. If that happens click on one of the other
objects' tabs and then back again or add this line of code to Sprinks'
Application.RefreshDatabaseWindow

--
Bill Mosca, MS Access MVP


Sprinks said:
To set a single tablename via code:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
db.TableDefs("OldName").Name = "NewName"

To put a "tbl" prefix on all table names:

Dim db As Database
Dim tdf As TableDef
Const conPrefix = "tbl"

Set db = CurrentDb()
For Each tdf in db.TableDefs
tdf.Name = conPrefix & tdf.Name
Next tdf

To reset names based on a table of old & new tablenames (named
OldNewNames)

Dim db As Database
Dim tdf As TableDef
Dim varNewName As Variant

Set db = CurrentDb()
For Each tdf In db.TableDefs
varNewName = DLookup("[NewName]", "OldNewNames", "[OldName]='" &
tdf.Name & "'")
If Not IsNull(varNewName) Then
tdf.Name = varNewName
End If
Next tdf

Hope that helps.
Sprinks

SysAccountant said:
Hi

Can anyone show me how to go about rename a table in code?



SysAccountant.
Click to expand...
Click to expand...
Click to expand...
 
You're welcome, Sprinks.

--
Bill Mosca, MS Access MVP


Sprinks said:
I wondered about that, Bill. Thanks.

Bill Mosca said:
After running Sprinks' code, you might not see the changes when you first
look in the database window. If that happens click on one of the other
objects' tabs and then back again or add this line of code to Sprinks'
Application.RefreshDatabaseWindow

--
Bill Mosca, MS Access MVP


Sprinks said:
To set a single tablename via code:

Dim db As Database
Dim tdf As TableDef

Set db = CurrentDb()
db.TableDefs("OldName").Name = "NewName"

To put a "tbl" prefix on all table names:

Dim db As Database
Dim tdf As TableDef
Const conPrefix = "tbl"

Set db = CurrentDb()
For Each tdf in db.TableDefs
tdf.Name = conPrefix & tdf.Name
Next tdf

To reset names based on a table of old & new tablenames (named
OldNewNames)

Dim db As Database
Dim tdf As TableDef
Dim varNewName As Variant

Set db = CurrentDb()
For Each tdf In db.TableDefs
varNewName = DLookup("[NewName]", "OldNewNames", "[OldName]='" &
tdf.Name & "'")
If Not IsNull(varNewName) Then
tdf.Name = varNewName
End If
Next tdf

Hope that helps.
Sprinks

:

Hi

Can anyone show me how to go about rename a table in code?



SysAccountant.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table structure modification 5
Manipulating backend linked tables 2
IIF statemen in SQL syntax- VBA code 2
Rename Multiple Tables at once 3
How do you rename a table? 3
"Calculate" on status bar 2
How to export table from outlook into ms access table? 0
how to rename a table using field name 1

Back
Top