Removing the dbo_ prefix from imported tables in MS Access

When importing data from SQL Server using the ODBC connector in MS Access, your tables will end up prefixed with their corresponding schema. In most cases this will be the "dbo" schema. If you need to remove the prefix, these are the steps you need to follow:

• Open your MS Access file (mdb or accdb)
• If you are presented with a Security Warning, enable execution of secure content
• Navigate to Database Tools and from the top left corner press the Visual Basic button
• This opens a new window
• Add a new module
• Replace everything in this module with the following

Public Sub Remove_Prefix()

    Dim obj As AccessObject
    Dim dbs As Object

    Set dbs = Application.CurrentData

    ' Search all tables 
    For Each obj In dbs.AllTables

        'Remove prefix if present
        If Left(obj.Name, 4) = "dbo_" Then
            DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
        End If

    Next obj
End Sub

• Run the code

All the "magic" is happening in the highlighted lines. Upon closing the Visual Basic window, you will be asked to save the module. This is up to you (if you need to reuse it in the future on the same file)

Happy coding...