Juri Strumpflohner
Juri Strumpflohner Juri is a full stack developer and tech lead with a special passion for the web and frontend development. He creates online videos for Egghead.io, writes articles on his blog and for tech magazines, speaks at conferences and holds training workshops. Juri is also a recognized Google Developer Expert in Web Technologies

HowTo: Create 'linked tables' in MS Access

2 min read

Assume you have to migrate some historical data from an application which used an MS Access database to a new application which uses some other DB system (in my case an Oracle DB). Well, if the underlying schema of both DBs is the same, it won't be a big deal, but if they differ from each other, it might become more complicated. In such a situation the best way is probably to create different queries in the MS Access DB which preprare your data s.t. it matches the structure of the Oracle DB schema. Once you've done that you can create so-called "linked tables" inside MS Access and then insert the data of your queries into these linked tables (can be done with an "append query").

How to create a "linked table" to an existing Oracle DB schema
  1. Right-click on the Navigation panel

  2. Choose "Link to the data source by creating a linked table"

  3. Go to the tab "Machine Data Source" and choose "New..."

  4. Choose the right driver

  5. Finally, provide the necessary access credentials

  6. The linked tables will have a special icon

Once you completed these steps, you can execute your queries (inserts, deletes, updates) against the linked tables. The changes will then be automatically reflected on your linked DB.
What you should do - especially if you know that you have to do the import multiple times with actualized data - is to automate the whole process. This can be easily achieved with a bit of VBA code as the following

Private Function executeQuery(ByVal queryName As String) As Boolean
On Error GoTo err

Dim dbs As Database

Set dbs = CurrentDb

If (queryName <> "") Then
Debug.Print "Execute query " & queryName
dbs.Execute (queryName)
End If

executeQuery = True
GoTo final

executeQuery = False
errorMsg = err.Description

Set dbs = Nothing
End Function

This code allows you to execute a query you've created on your MS Access DB. The rest should be obvious and if you want to make it more user-friendly your outcome may look similar to the following:

Questions? Thoughts? Hit me up on Twitter
comments powered by Disqus