Because of the flexibility of ODBC, you can use Access as a “junction box” to join up
several different databases if required. It does not matter if they use different platforms, so
long as they are ODBC-compatible.
For example, you could put links in your Access database to tables on an Oracle database
and a SQL Server database. Provided there are key fields that link between the two, such as a
customer ID reference or an order number, you can produce data that straddles the two systems.
I have found this very useful for reconciling data held both on SQL Server and Oracle. It
would normally be very difficult to create a query on SQL Server that used Oracle tables, but
Access and ODBC can provide a solution.
Linking to external databases also makes your Access application smaller because all the
data is being held elsewhere. Connecting to other Access databases is a way of getting round
the 2GB limit on Access. You use your master database to hold the queries, forms, reports,
and VBA code, and the data is stored in another Access database or an external database such
as SQL Server.
Using a heavy duty database such as SQL Server or Oracle can also make your application
more robust.Linking to Other Access Databases
Access provides a very easy way to create linked tables to other Access databases. Click
External Data on the Access menu and then click Access in the Import group of the ribbon.
On the pop-up screen, use the Browse button to locate your database and click the “Link to
Data Source by Creating a Linked Table” radio button.
When locating the file in the browser, try and use the actual URL for the server instead of
the drive letter. For example, you may have a particular server mapped to J, but another user
will have it mapped to H. If you distribute your application to this user, it will not work
unless they re-map their drive. Using the URL gets around this ambiguity.
Click the OK button and a pop-up listing all the tables in that database will display. Select
the ones you need and click OK.
These will appear as linked tables in the navigation pane. If you double-click the table,
you will see the data held in it. You can use these linked tables as if they were part of your
database, except that you cannot alter the structure of the table.ODBC Links and DSNs
Things are more complicated if you wish to link to an external database such as SQL Server
or Oracle. To make the connection, you will need four parameters. These are the server name
where the database is held, a login ID and password that have been issued to you by the
database owner (do not use somebody else’s), and the name of the database.240 Microsoft Access 2010 VBA Macro Programming