Code to rename tables

  • Thread starter Thread starter LisaB
  • Start date Start date
L

LisaB

I have just imported 50 tables from SQL to Access and they all have the dbo
name in front of the table name

dbo_tblCustomers

I would like to know if there is VBA code to go through the table and drop
the "dbo_ " that appears before each tables name.


Thank you
 
Try something along the lines of
Dim tbl As DAO.tabledef

For Each tbl In TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next

Hope This Helps
Gerald Stanley MCSD
 
I thought the Name property was read-only. I had to try
this to verify it.....


I always used the DoCmd.Rename method to rename objects...

I did find that QueryDef.Name is also read/write, but
dbs.Containers("Forms").Documents("Form1").Name is Read
Only....

I guess you learn something every day.


Chris Nebinger
 
I get a runtime error 424 Object required

Dim tbl As TableDef
Dim TD As TableDefs

For Each tbl In TD ****(Error - code stops Here)*****
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next
 
Actually, you don't need the TD object, but you want to instantiate a
reference to the Database:

Dim db As DAO.Database
Dim tbl As DAO.TableDef

Set db = CurrentDb()
For Each tbl In db.TableDefs
If Left$(tbl.Name, 4) = "dbo_" Then
tbl.Name = Mid$(tbl.Name, 5)
End If
Next tbl


For Each tbl In CurrentDb().TableDefs
 
Your runtime error is because your code doesn't set the object variables to
anything.

Need:
Set TD=CurrentDb.TableDefs
Then the For Each loop should be able to return the tabledefs.

Paul Johnson
 
Back
Top