Create a list of tables

  • Thread starter Thread starter Joe
  • Start date Start date
J

Joe

Hello All:
I'm looking to create a list of all of the tables in a
dB. I would like to edit this list using the rename
method. My goal is simply to rename all of the tables in
bulk. I only need to remove a prefix from each table.
Thanks in advance for any help.
Joe
 
Here is some sample code that removes the dbo_ prefix from
tables.

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

Hope This Helps
Gerald Stanley MCSD
 
Joe said:
Hello All:
I'm looking to create a list of all of the tables in a
dB. I would like to edit this list using the rename
method. My goal is simply to rename all of the tables in
bulk. I only need to remove a prefix from each table.
Thanks in advance for any help.
Joe
Click to expand...

Code along these lines should do it:

'----- start of procedure -----
Sub RemoveTablePrefix(strPrefix As String)

Dim db As DAO.Database
Dim tdf As DAO.TableDef

If strPrefix = "MSys" Then
Msgbox "You may not rename system tables!"
Exit Sub
End If

Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Name Like strPrefix & "*?" Then
tdf.Name = Mid(tdf.Name, Len(strPrefix) + 1)
End If
Next tdf
Set db = Nothing
Application.RefreshDatabaseWindow

End Sub

'----- end of procedure -----

You'd call it like this:

RemoveTablePrefix "xxx"

(where "xxx" is the prefix to be removed).
 
Back
Top