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
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
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.
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
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
