MySQL and ODBC - Importing and Linking the Data Source (
Page 4 of 6 )
Given the initial confusion caused
by connecting a MS Access database to a ODBC data source, a few clarifications
are in order. There are essentially two functions provided by MS Access which
are used to associate the MS Access database with the data source, each
accessible via the File-->Get External Data
submenu:
ImportThe Import option should be used if any data
found in the data source will be subsequently used exclusively in MS Access.
That is to say that no other applications will have the data source data
available to them.
Link TablesThe Link Tables option should be
used if the data found in the data source will be updated using applications
other than the MS Access. This is likely the choice that the user will want to
employ, since it is the intention that MS Access will update the data stored
within the MySQL database server in addition to its own internal
tables.
Therefore, choose Link Tables from the Get External Data submenu.
Doing so will create a "link" between the data source tables and MS Access. Any
information updated using the Access Interface (which we’ll create in the next
section), will automatically update the MySQL database, making the information
immediately available from any application using that data. Choosing Link Tables
will result in the familiar looking navigation window to appear. However,
nothing will actually be selected in the window. Instead, go to the Files of
type drop-down menu located at the bottom of the window, and choose ODBC
Databases(). This is illustrated in Figure 1-5.
Figure 1-5: Choosing ODBC
Databases()
Doing so will cause yet
another window to appear. Choosing the tab entitled Machine Data Source will
produce a window very similar to that shown in Figure 1-6. Notice that the
widget DSN is included in the data source name listing. Click on the widget DSN
and press OK. This will produce one final window, which lists all of the tables
found in the widget database, in this case only one, clients. Click on the
clients table and press OK. The window will close and the original database
window (Shown in Figure 1-4) will reappear, this time including the names of
those tables chosen in the window. Next to each table name will be a globe with
an arrow pointing to it. This signifies that these tables have been linked to
those found in the data source.
Note: The client machine must be
connected to the Internet, otherwise it will be impossible for MS Access to
update the new database with the information found in the data source
tables!
Figure 1-6: Machine Data Source window
Double-click the clients table name, and window will
appear displaying the table structure. Notice that the columns are the same as
those found within the MySQL clients table created earlier in this
tutorial.
Congratulations. An ODBC connection to a MySQL database has
been successfully carried out. In the next section, a demonstration of how
inserting information into the linked tables via the Access database will cause
the MySQL database to be simultaneously updated.
