Access: Remove “dbo_” prefix from imported tables

The procedure below, can be used to remove the “dbo_” prefix from all imported tables within a Microsoft Access Database.

Please ensure you test any script taken from my website on a test/development machine, before running on a production server

Public Sub Remove_DBO_Prefix()
    Dim obj As AccessObject
    Dim dbs As Object
    Set dbs = Application.CurrentData
    'Search for open AccessObject objects in AllTables collection.
    For Each obj In dbs.AllTables
        'If found, remove prefix
        If Left(obj.Name, 4) = "dbo_" Then
            DoCmd.Rename Mid(obj.Name, 5), acTable, obj.Name
        End If
    Next obj
End Sub

VB: The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on local machine

I was in the middle of writing an application to retrieve the list of Access Database Tables, but I was getting the error “The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine.”

I discovered, that as I was using Vista x64, I needed to change the “Advanced Compiler Options” in Visual Studio to target the “x86” CPU, thereby creating a 32-bit application.

Advanced Compiler Options

When I tried again, it worked