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- Right-click on the Navigation panel
- Choose "Link to the data source by creating a linked table"
- Go to the tab "Machine Data Source" and choose "New..."
- Choose the right driver
- Finally, provide the necessary access credentials
- 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
err:
executeQuery = False
errorMsg = err.Description
final:
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
