How to programmatically rename table names in access

[Jack]

Hi,
I have an access database linked to sql server database. The name of the
tables are: dbo_Student, dbo_Score etc. There are altogether 20 tables. How
does one rename the tables programmatically by taking out the dbo_ prefix
from each of the tables. Thanks.

 

[Jeff Boyce]

Jack

Why?

Each time you (re-)link to those tables, you'd have to (re-)remove the
prefix. Why not just leave the dbo_ prefix as part of the tablename?

Please note -- I'm not asking out of prurient interest, but to try to
determine what your underlying business need is to change the table names.
More specific descriptions lead to more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

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

 

[John W. Vinson]

Hi,
I have an access database linked to sql server database. The name of the
tables are: dbo_Student, dbo_Score etc. There are altogether 20 tables. How
does one rename the tables programmatically by taking out the dbo_ prefix
from each of the tables. Thanks.

I agree with Jeff that this is often unnecessary - you can use captions,
forms, aliases etc. to display any name for any table. But (because I have a
frontend that must be able to link to JET or SQL tables), I did toss this
little routine together:

Public Sub renamedbo()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
For Each tdf In db.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Mid(tdf.Name, 5)
End If
Next tdf
End Sub

John W. Vinson [MVP]