Easily remove dbo from tables in Microsoft Access

When you link a Microsoft Access database to a MSSQL server it automatically adds dbo_ to all of the table names. This can be very frustrating if you are manually renaming this tables every time you re-link them.

Here is an easy step by step approach if you'd like to automatically remove the dbo prefix from the table names.

Step 1. Create a new form in design view and add a new button.

Step 2. Select the new button and go to the "Event" tab in the properties of the button. Change the "On Click" event to [Event Procedure] and then select the three dots icon next to it "..." This will bring up the Visual Basic code editor.

Step 3. Copy the code below before the "End Sub" tag.

Dim SQL As String Dim DB As database Dim RS As Recordset

SQL = "SELECT Name " & _ "FROM MSysObjects " & _ "WHERE (((Left([Name],4))='dbo_'));"

Set DB = CurrentDb() Set RS = DB.OpenRecordset(SQL)

If RS.EOF = False Then RS.MoveFirst

Do Until RS.EOF DoCmd.Rename Mid(RS!name, 5, 100), acTable, RS!name RS.MoveNext Loop RS.Close End If

Step 4. Close the Visual Basic editor window and save your new form. Open the form in Form View and click the button you created.

All of your tables will now have been renamed without the dbo_ prefix. You can keep this form in your database to use when you link any new tables.