Rename a table in code (NEED HELP, PLEASE, PLEASE)

  • Thread starter Thread starter Steven Ferguson
  • Start date Start date
S

Steven Ferguson

Hello,

I want to take an existing table (linked) in my database
and rename that table to something different. I know that
I can simply go into the database design view and rename
the object that way, but I want to do this through code.

In other words...

Before:
tbl_NameBefore

After source code (which I have no idea how to write)
is applied:

tbl_NameAfter

What Access 97 code would I write to make this happen?
Thanks
 
How to Change a Table Name in Code:

====================================

In code you can call the function below:

ChangeTableName("OldTableName", "NewTableName")

====================================
Paste this code into a module:

Public Sub ChangeTableName(mOldName As String, mNewName As String)
On Error GoTo Err_ChangeTableName

Dim db As Database, tdf As TableDef, strSQL As String, qdf As QueryDef
Set db = CurrentDb

If TableExists(mNewName) Then
MsgBox ("The new name already exists. Please choose a different name.")
GoTo Exit_ChangeTableName
End If

'Change table name
db.TableDefs(mOldName).Name = StrConv(mNewName, vbUpperCase)

Exit_ChangeTableName:
Set db = Nothing
Exit Sub

Err_ChangeTableName:
MsgBox "Error # " & Err.Number & " was generated by " & Err.Source &
vbCrLf & Err.Description, , "SystemCode - ChangeTableName"
Resume Exit_ChangeTableName

End Sub


Function TableExists(TableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(TableName))
End Function
 
Back
Top