Rename a Table in MS Access using VB or SQL statement

Hi all,

How can I rename an MS Access table in VB or thro' SQL statement

I have 2 clues as given below, but I am not able to get to use these ideas.
These statements does not seem to refer the ADO library and hence, I do not know how to arrive at framing a program for these statements.

Please help me in using these clues by guiding me how to frame a program around the statements or please let me know if there are any alternative ways.

Clue 1:

This creates a copy of the original table and you then delete the original leaving you with a newly named table.

DoCmd.CopyObject, "myTableOld", acTable, "myTable"
DoCmd.DeleteObject acTable, "myTable"

-----------------------------

Clue 2:

CurrentDB.TableDefs("myTable").Name = "myTableOld"

Any help much appreciated.

MNRaghu

Answer this question

Rename a Table in MS Access using VB or SQL statement

Ken Wilson

Hi,

Are you still using VB6 or VB.net if your into VB6 then you could take a look at the ADOX (ADO Extension) library. This library modifies database objects through VB code. I don't know any existing function in access-sql to be able to modify a table name. But in sqlserver you can use the sp_rename function to rename a table...

cheers,

Paul June A. Domag
M.D.luffy
Renee,

Thanks for the information. Should be an issue for me to look into for future.

Raghu
Melle Dorel
A translation to VB.Net 2005:

Private Sub RenameTables(ByVal sTextToRemove as String)
Dim i As Integer
Dim dbRename As Database
Dim Connect As New PrivDBEngine

dbRename = Connect.OpenDatabase(tbDBPath.Text)
dbRename.CreateTableDef()

For i = 0 To dbRename.TableDefs.Count - 1
If dbRename.TableDefs(i).Name.Length >= sTextToRemove.Length - 1 Then
If dbRename.TableDefs(i).Name.Substring(0, 9) = sTextToRemove Then
dbRename.TableDefs(i).Name = dbRename.TableDefs(i).Name.Substring(9)
End If
End If
Next i

dbRename.Close()
dbRename = Nothing

MessageBox.Show("Tables in " + tbDBPath.Text + " have been renamed.", _ "Rename Access Tables", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub
Amit Bhave

I have succesfully used ADOX with Access Databases and they work well.

I don't have much access to Access internals, but I'm wondering if renaming the entry in MSysObjects is all it would require. I did a lot of experiementing with that and I ended up not going that route. I can't exactly remember why, I just rmrmber that things didn't go well.
Joshua_Ethan
WHOOO HOOOO!!!!

Thanks guys, you solved a huge problem for me.

i had about 400 tables to rename after importing them from SQL server 2000 into Access because the table names get prefixed with "dbo_", and i dont know if that can be disabled somehow.

... i altered MNRaghu's code to this:

Sub RenameTables()
Dim i As Integer
Dim dbRename As Database

Set dbRename = OpenDatabase(App.Path & "\mydb.mdb")

dbRename.CreateTableDef

For i = 0 To dbRename.TableDefs.count - 1
If Left(dbRename.TableDefs(i).Name, 4) = "dbo_" Then
dbRename.TableDefs(i).Name = Replace(dbRename.TableDefs(i).Name, "dbo_", "")
End If
Next i

dbRename.Close
Set dbRename = Nothing

End Sub
gabriewo
Never mind, I got it. (Used DAO 3.6 as reference.)

Public Sub rentbl()
Dim dbSS As Database

Dim strDbName As String
strDbName = "c:\temp\test.mdb"

Set dbSS = OpenDatabase(strDbName)

dbSS.CreateTableDef
dbSS.TableDefs("R2").Name = "Org_R2"

dbSS.Close
Set dbSS = Nothing

End Sub

Thanks,
MNRaghu
dodo.net
Thanks for the information Paul June.
I'll surely look into the ADOX library too.

MNRaghu
Rename a Table in MS Access using VB or SQL statement

Answer this question