Once upon a time, a long long time ago, I got contracted to show a government office how to build and deliver applications… in Microsoft Access. I’m sorry. I’m so, so sorry. As horrifying and awful as it is, Access is actually built with some mechanisms to actually support that- you can break the UI and behavior off into one file, while keeping the data in another, and you can actually construct linked tables that connect to a real database, if you don’t mind gluing a UI made out of evil and sin to your “real” database.

Which brings us to poor Alex Rao. Alex has an application built in Access. This application uses linked tables, which he wants to convert to local tables. The VBA API exposed by Access doesn’t give him any way to do this, so he came up with this solution…

Public Function convertToLocal()
    Dim dBase As DAO.Database
    Dim tdfTable As DAO.TableDef

    Set dBase = CurrentDb

    For Each tdfTable In dBase.TableDefs
        If tdfTable.Connect <> "" Then
            ' OH. MY. GOSH. I hate myself so much for doing this. For the love of everything holy,
            ' dear reader, if you can come up with a better way to do this, please tell me about it
            ' AS SOON AS POSSIBLE
            ' I have literally been trying to do this for the past week. For reference, here is what I
            ' am trying to do:
            '   Convert a "linked" table to a "local" one
            '   Keep relationships intact.
            ' Now, Access has this handy tool, "Convert to Local Table" - you'll see it if you right click
            ' on a linked table. However, THERE IS NO WAY IN VBA TO DO THIS. I am aware of the following:
            '   DoCmd.SelectObject acTable, "Company", True RunCommand acCmdConvertLinkedTableToLocal
            ' Note that this no longer works as of Access 2016 because the wonderful programmers at Microsoft decided
            ' that "It wasn't used anymore".
            '
            ' So, onto my solution:
            '   First, I select the table object, making sure it's actually selected (i.e., like a user selected it)
            '   Then, I pause for one second (I hope to the man upstairs that's long enough)
            '   Then, I send the "Context Menu" key (SHIFT+F10)
            '   Then, I pause for another second (Again, fingers crossed)
            '   Then, I send the "v" key - to activate the "ConVert to Local Table" command shortcut
            '
            ' I literally send KEYPRESSES to the active application, and hope to God that Access is ready to go.
            ' And if the user selected a different application (or literally anything else) in that time? Well,
            ' then Screw you, user.
            '
            ' God help us.
            DoCmd.SelectObject acTable, tdfTable.Name, True
            Pause 1
            SendKeys "+{F10}", True
            Pause 1
            SendKeys "v", True
        End If
    Next tdfTable
End Function

Don’t feel bad, Alex. I’m certain this isn’t the worst thing ever built in Access.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!